| Can a procedure have multiple OUT sy_refcursor [message #604997] |
Mon, 06 January 2014 16:46  |
gentleman777us
Messages: 122
Registered: April 2005
|
Senior Member |
|
|
Hi,
Can a Procedure have multiple sys_refcursor OUT parameters?
ex:
procedure test(userid IN NUMBER,
O_shppingagencies OUT sys_refcursor,
o_exporters OUT sys_refcursor);
if it is ok to have more than one sys_refcursor ouputs how do you execute them
ex: OPEN O_shppingagencies FOR v_shippersql;
OPEN o_exporters FOR v_exportersql;
Currently when I execute it says CURSOR is closed.
Am I doing anything wrong.
-
|
|
|
|
|
|
|
|
|
|
| Re: Can a procedure have multiple OUT sy_refcursor [message #605040 is a reply to message #604997] |
Tue, 07 January 2014 02:13  |
 |
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
SQL> create or replace procedure p (c1 out sys_refcursor, c2 out sys_refcursor)
2 is
3 begin
4 open c1 for select * from emp;
5 open c2 for select * from dept;
6 end;
7 /
Procedure created.
SQL> var c1 refcursor
SQL> var c2 refcursor
SQL> exec p(:c1,:c2)
PL/SQL procedure successfully completed.
SQL> print c1 c2
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17/12/1980 00:00:00 800 20
7499 ALLEN SALESMAN 7698 20/02/1981 00:00:00 1600 300 30
7521 WARD SALESMAN 7698 22/02/1981 00:00:00 1250 500 30
7566 JONES MANAGER 7839 02/04/1981 00:00:00 2975 20
7654 MARTIN SALESMAN 7698 28/09/1981 00:00:00 1250 1400 30
7698 BLAKE MANAGER 7839 01/05/1981 00:00:00 2850 30
7782 CLARK MANAGER 7839 09/06/1981 00:00:00 2450 10
7788 SCOTT ANALYST 7566 19/04/1987 00:00:00 3000 20
7839 KING PRESIDENT 17/11/1981 00:00:00 5000 10
7844 TURNER SALESMAN 7698 08/09/1981 00:00:00 1500 0 30
7876 ADAMS CLERK 7788 23/05/1987 00:00:00 1100 20
7900 JAMES CLERK 7698 03/12/1981 00:00:00 950 30
7902 FORD ANALYST 7566 03/12/1981 00:00:00 3000 20
7934 MILLER CLERK 7782 23/01/1982 00:00:00 1300 10
14 rows selected.
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
4 rows selected.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
