Home » SQL & PL/SQL » SQL & PL/SQL » Can a procedure have multiple OUT sy_refcursor (oracle 11g)
Can a procedure have multiple OUT sy_refcursor [message #604997] Mon, 06 January 2014 16:46 Go to next message
gentleman777us
Messages: 117
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 #604998 is a reply to message #604997] Mon, 06 January 2014 17:03 Go to previous messageGo to next message
BlackSwan
Messages: 22780
Registered: January 2009
Senior Member
>Am I doing anything wrong.
any error generally means that you did something wrong.

we can't know what you did wrong since you decided to not show us exactly what you did that resulted in some error.

Is COPY & PASTE broken for you?

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: Can a procedure have multiple OUT sy_refcursor [message #604999 is a reply to message #604998] Mon, 06 January 2014 17:10 Go to previous messageGo to next message
gentleman777us
Messages: 117
Registered: April 2005
Senior Member
I get the error 'Cursor is closed" When I execute.
Re: Can a procedure have multiple OUT sy_refcursor [message #605000 is a reply to message #604999] Mon, 06 January 2014 17:19 Go to previous messageGo to next message
BlackSwan
Messages: 22780
Registered: January 2009
Senior Member
we can't know what you did wrong since you decided to not show us exactly what you did that resulted in some error.

Is COPY & PASTE broken for you?

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: Can a procedure have multiple OUT sy_refcursor [message #605040 is a reply to message #604997] Tue, 07 January 2014 02:13 Go to previous message
Michel Cadot
Messages: 59076
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.

Previous Topic: How to Display Multiple rows in a single row but in different columns
Next Topic: Dynamic Column List in Select query
Goto Forum:
  


Current Time: Tue Sep 16 03:06:21 CDT 2014

Total time taken to generate the page: 0.08648 seconds