Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
how to return a recordset or cursor from a stored procedure
- Thread starter ddiamond
- Start date
SantaMufasa
Technical User
Yes, "refcursors" are probably your solution.
I'll try to post an example but I must leave to undergo a routine medical procedure in just a few minutes and may not have sufficient time just now.
I'll be back.
![[santa]](/data/assets/smilies/santa.gif "[santa] [santa]")
Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
I'll try to post an example but I must leave to undergo a routine medical procedure in just a few minutes and may not have sufficient time just now.
I'll be back.
Mufasa(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
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 option
JServer 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 - Production
With the Partitioning option
JServer 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;
begin
xxx.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 @mytest
SQL*Plus: Release 9.2.0.2.0 - Production on Tue Nov 7 15:19:45 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 option
JServer 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 - Production
With the Partitioning option
JServer Release 9.2.0.2.0 - Production
$
$ 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 option
JServer 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 - Production
With the Partitioning option
JServer 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;
begin
xxx.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 @mytest
SQL*Plus: Release 9.2.0.2.0 - Production on Tue Nov 7 15:19:45 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 option
JServer 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 - Production
With the Partitioning option
JServer Release 9.2.0.2.0 - Production
$
BJCooperIT
Programmer
Code:
CREATE OR REPLACE PACKAGE types_pkg IS
TYPE typdef_ref_cursor IS REF CURSOR;
TYPE typdef_record_num IS RECORD (r_number NUMBER);
END;
/
CREATE OR REPLACE PROCEDURE test_prc
( in_date IN DATE,
out_cursor OUT types_pkg.typdef_ref_cursor )
IS
v_last_day number := to_char(last_day(in_date),'dd');
BEGIN
OPEN out_cursor FOR
SELECT ROWNUM
FROM all_objects
WHERE ROWNUM BETWEEN 1 AND v_last_day;
END test_prc;
/
Code:
SET serveroutput ON SIZE 25000
DECLARE
v_cursor types_pkg.typdef_ref_cursor;
v_dom_row types_pkg.typdef_record_num;
v_dom NUMBER;
BEGIN
test_prc(SYSDATE, v_cursor);
LOOP
FETCH v_cursor INTO v_dom_row;
EXIT WHEN v_cursor%NOTFOUND;
dbms_output.put_line(v_dom_row.r_number);
END LOOP;
CLOSE v_cursor;
END;
/
Code:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30[sup]Beware of false knowledge; it is more dangerous than ignorance.[/sup][sup] ~George Bernard Shaw[/sup]
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: www.EmuProductsPlus.com
- Thread starter
- #5
- Status
Similar threads