Re: Returning result set from procedure
Date: 1996/11/27
Message-ID: <329bb734.5380136_at_dcsun4>#1/1
The answer is cursor variables....
PL/SQL can return pl/sql tables (like arrays) to clients. Additionally, pl/sql can open a cursor that a 3gl fetches from as such (the following example, for 7.2 and up, demonstrates how to open a cursor in pl/sql that depends on the inputs):
create or replace package empCurType
as
cursor c1 is select ename, mgr, dept.deptno, dname, loc from emp, dept;
type empCur is ref cursor return c1%rowtype;
procedure open_cursor( p_cursor in out empCur, p_deptno in number );
end;
/
show errors
create or replace package body empCurType as
procedure open_cursor( p_cursor in out empcur, p_deptno in number )
is
begin
if ( p_deptno in ( 10, 20 ) ) then -- open local query
open p_cursor for
select ename, mgr, dept.deptno, dname, loc
from emp, dept
where emp.deptno = dept.deptno
and dept.deptno = p_deptno;
else
open p_cursor for
select ename, mgr, dept.deptno, dname, loc
from emp_at_aria.world, dept_at_aria.world
where emp.deptno = dept.deptno
and dept.deptno = p_deptno;
end if;
end open_cursor;
end empCurType;
/
show errors
And then the pro*c would look like 'normal' pro*c code except that the EXEC SQL OPEN Cursor statement would now be a pl/sql call as follows:
static void process()
{
EXEC SQL BEGIN DECLARE SECTION;
typedef char asciiz; EXEC SQL TYPE asciiz IS STRING(100); SQL_CURSOR my_cursor; asciiz ename[100]; int mgr; short mgr_i; int deptno; asciiz dname[100]; asciiz loc[100]; int i;
EXEC SQL END DECLARE SECTION; EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();
EXEC SQL ALLOCATE :my_cursor;
EXEC SQL EXECUTE BEGIN empCurType.open_cursor( :my_cursor, 10 ); end;
END-EXEC;
for( ;; )
{
EXEC SQL WHENEVER NOTFOUND DO BREAK;
EXEC SQL FETCH :my_cursor
INTO :ename, :mgr:mgr_i, :deptno, :dname, :loc;
printf( "%s, %d, %d, %s, %s\n",
ename, mgr_i?-1:mgr, deptno, dname, loc );
}
EXEC SQL CLOSE :my_cursor;
}
On Tue, 26 Nov 1996 20:47:34 -0400, slanza_at_digitalid.com (Stephen Lanza) wrote:
>I have only been using Oracle for a few months and cannot find out how to
>return a result set to my front-end application from a stored procedure. I
>was able to do this without problem from Sybase and I miss the ability to
>do it.
>
>My question is: Is it possible to get a result set from a stored procedure
>that I can fetch from my application or is this specifically prohibited in
>Oracle?
>
>Thanks for any help.
>
>Stephen Lanza
>
>Software Complement | Email: slanza_at_digitalid.com
>8 Pennsylvania Avenue | Voice: 717-491-2492
>Matamoras, PA 1833 | Voice: 717-686-1898
Thomas Kyte
Oracle Government
tkyte_at_us.oracle.com
- Check out Oracle Governments web site! -----
Follow the link to "Tech Center"
and then downloadable Utilities for some free software...
statements and opinions are mine and do not necessarily reflect the opinions of Oracle Corporation Received on Wed Nov 27 1996 - 00:00:00 CET