Board index » delphi » HELP returning >1 value from a PL/SQL block

HELP returning >1 value from a PL/SQL block

Hi All,

I am attempting to return more than one value from a PL/SQL(vers 2.3)
block into delphi.  I can easily get one value back using return(foo)
but when I try to return multiple values I have no success.  I have
assumed that the way to do this is to create a PL/SQL table, populate it
and return it.  However, Delphi returns the error 'ORA-6550: 'GETMET2'
is not a procedure or is undefined.  But it is there, as a function!

My code is...

create or replace function getmet2(flag IN char)
return metvar%rowtype
is
begin
declare
        cursor c1 is
        select * from METVAR
        where DELETED= flag;
        MetTab METVAR%ROWTYPE;
begin
open c1;
loop
        fetch c1 into MetTab;
        exit when c1%notfound;
        return (MetTab);
end loop;
end;
end;

I then have a tStoredProc component on the form which is opened at
run-time.

Many TIA for your help

Simon

 

Re:HELP returning >1 value from a PL/SQL block


Quote
In article <318E9855.2...@hort.cri.nz>, Simon Keys <sk...@hort.cri.nz> wrote:
>Message-ID: <318E9855.2...@hort.cri.nz>
>Date: Tue, 07 May 1996 13:24:53 +1300
>From: Simon Keys <sk...@hort.cri.nz>
>Organization: HortResearch
>X-Mailer: Mozilla 2.0 (Win95; I)
>MIME-Version: 1.0
>Newsgroups: comp.lang.pascal.delphi.databases,comp.databases.oracle
>Subject: HELP returning >1 value from a PL/SQL block
>NNTP-Posting-Host: 161.66.1.25
>Lines: 30
>Path: news.interlog.com!news2.interlog.com!winternet.com!uunet!in2.uu.net!newsfeed.internetmci.com!howland.reston.ans.net!news.starnet.net!waikato!news.massey.ac.nz!news.palm.cri.nz!
>Xref: news.interlog.com comp.lang.pascal.delphi.databases:16030 comp.databases.oracle:74426
>Status: N

>Hi All,

>I am attempting to return more than one value from a PL/SQL(vers 2.3)
>block into delphi.  I can easily get one value back using return(foo)
>but when I try to return multiple values I have no success.  I have
>assumed that the way to do this is to create a PL/SQL table, populate it
>and return it.  However, Delphi returns the error 'ORA-6550: 'GETMET2'
>is not a procedure or is undefined.  But it is there, as a function!

>My code is...

>create or replace function getmet2(flag IN char)
>return metvar%rowtype
>is
>begin
>declare
>    cursor c1 is
>    select * from METVAR
>    where DELETED= flag;
>    MetTab METVAR%ROWTYPE;
>begin
>open c1;
>loop
>    fetch c1 into MetTab;
>    exit when c1%notfound;
>    return (MetTab);
>end loop;
>end;
>end;

>I then have a tStoredProc component on the form which is opened at
>run-time.

>Many TIA for your help

>Simon

Delphi's TStoredProc will only look at stored procedure, not stored functions.  
There is quite the difference between the two.  To access a stored function you need
to call the functions from within a TQuery component and set the SQL property to
something like:

Select getValue( param1, param2) as NewVAl
from dual

This will should work from the SQL*Plus command line as well.

To return multiple values, you'll need to create your code as a stored procedure with
one or more of the parameters set as either 'in out' or just 'out'. A stored function,
by definition, can only return one value and since Delphi knows nothing about Oracle's
cursor or PL/SQL tables it will never see them.

Other Threads