Re: Can't return multiple rows in PL/SQL<->OCI call???
Date: 1998/01/28
Message-ID: <34d6ae2b.13220159_at_192.86.155.100>#1/1
On Tue, 27 Jan 1998 20:01:32 -0600, dave_at_digidem.com wrote:
>I'm in the process of learning the OCI functions so i can port some
>Sybase data access functions. In my app all data access is retrieved
>using stored procedures. The programmer calls a function that binds the
>input and output variables and then calls the stored procedure.
>
>My question is: If i want to return a result set with multiple rows back
>to an OCI application, then do i have to use a PL/SQL cursor? Isn't there
>a way to map the result set to my variables and then perform a fetch to
>get each row? If i do a
>
In Oracle, you'll use a cursor variable (see pl/sql manual for info on that). You'll open the cursor in pl/sql and bind and fetch from it in C. Here is a really small example:
- pl/sql code ---------------------------------------------------
create or replace package demo_pkg
as
type genCur is ref cursor;
procedure return_set( p_theCursor in out genCur );
end;
/
create or replace package body demo_pkg
as
procedure return_set( p_theCursor in out genCur )
is
begin
open p_theCursor for select ename from emp; end;
end;
/
And here is the C code in O7 OCI that calls the stored procedure, gets the result set and prints it out:
- OCI Code ---------------------------------------------------
void demo_result_set( void )
{
text ename[11];
text sqlstmt[1024];
Cda_Def c1;
strcpy( sqlstmt, "begin demo_pkg.return_set( :C1 ); end;" );
if (oparse(&cda, sqlstmt, (sb4)-1, 0, (ub4)VERSION_7))
print_error_and_exit(&cda);
memset( &c1, 0, sizeof(c1) );
if (obndra(&cda, (text *) ":C1", -1, (ub1 *) &c1,
-1, SQLT_CUR, -1, (sb2 *) 0, (ub2 *) 0, (ub2 *) 0,
(ub4) 0, (ub4 *) 0, (text *) 0, 0, 0)) print_error_and_exit(&cda);
if (oexec(&cda)) print_error_and_exit(&cda);
if (odefin(&c1, 1, (ub1 *) ename, (sword) sizeof(ename),
SQLT_STR, -1, (sb2 *) -1, (text *)0, (sword) 0, (sword) 0,
(ub2 *) 0, (ub2 *) 0)) printf( "error binding ename\n" );
while (1)
{
if (ofetch(&c1))
{
if (c1.rc == 1403) break;
else print_error_and_exit( &c1 );
}
else fprintf(stdout, "%s\n", ename );
}
}
So basically, in oracle you will:
open a cursor to execute the stored procedure (i already opened an LDA -- logon data area and CDA -- cursor data area in my logon routine)
parse the statement for the procedure (oparse above) bind the cursor datatype to the in/out parameter to the procedure (obdnra) execute the cursor -- run the procedure (oexec)
bind the output variables from the query (odefin). See cdemo5.c in $ORACLE_HOME/rdbms/demo for an example of describing the cursor returned so you could dynamically figure out the number of columns and the datatype.
loop
fetch (ofetch)
exit when no more data
do something with the row
end loop
Hope this helps...
>SELECT bob INTO :bobvar
>
>What happens if there are many bob rows? It sounds like calling PL/SQL
>from an OCI app isn't worth the trouble. Any benefit i might get from
>using stored procs seems wasted in these details.
>
>Are my above assumptions correct, or am i reading the OCI mannual wrong?
>It just seems so backwards compared to Sybase's API (client-lib). What am
>i missing?
>
>
>thanks!
>
>-david m rosner
>
>-------------------==== Posted via Deja News ====-----------------------
> http://www.dejanews.com/ Search, Read, Post to Usenet
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD
http://govt.us.oracle.com/ -- downloadable utilities
Opinions are mine and do not necessarily reflect those of Oracle Corporation
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Wed Jan 28 1998 - 00:00:00 CET