​A selection of topics on IT and its application to finance. 
​Send me your comments, questions ​or suggestions by clicking
h​ere


elmama
 How to return a recordset or cursor from a stored procedure

My response to the above question on one of the technical forums I visit
Try this for size - should be OK under V8 too

$ type mypack.sql

create or replace package xxx as type myrec is record (name varchar2(20));
type mycur is ref cursor return myrec;
procedure myproc (cur_param in out mycur);
end xxx;
/
show error

create or replace package body xxx as
procedure myproc(cur_param IN OUT mycur)
is
begin
open cur_param for select ename from scott.emp;
end myproc;
end xxx;
/
show error
exit

​$ sqlplus scott/tiger @mypack

SQL*Plus: Release 9.2.0.2.0 - Production on Tue Nov 7 15:14:01 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:Oracle9i Enterprise Edition Release 9.2.0.2.0 - Production
With the Partitioning optionJServer Release 9.2.0.2.0 - Production

Package created.
No errors.

Package body created.
No errors.
​Disconnected from Oracle9i Enterprise Edition Release 9.2.0.2.0 - ProductionWith the Partitioning optionJServer Release 9.2.0.2.0 - Production

Now the test stub to test it
$ type mytest.sql

set serveroutput on
declare testcur xxx.mycur;
testrec xxx.myrec;
beginxxx.myproc(testcur);
loop
fetch testcur into testrec;
exit when testcur%NOTFOUND;
dbms_output.put_line(testrec.name);
end loop;
close testcur;
end;
/
exit
$
$
​$ sqlplus scott/tiger @mytestSQL*Plus: Release 9.2.0.2.0 - Production on Tue Nov 7 15:19:45 2006Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.Connected to:Oracle9i Enterprise Edition Release 9.2.0.2.0 - ProductionWith the Partitioning optionJServer Release 9.2.0.2.0 - Production
​SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
​SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER

PL/SQL procedure successfully completed.Disconnected from Oracle9i Enterprise Edition Release 9.2.0.2.0 - ProductionWith the Partitioning optionJServer Release 9.2.0.2.0 - Production$$