Re: How do I view SYS_REFCURSOR output parameters in TOAD?

From: tinojam <tinojam_at_gmail.com>
Date: 10 Mar 2006 16:31:38 -0800
Message-ID: <1142037098.579251.108890@e56g2000cwe.googlegroups.com>

I don't think you can achieve what you want in TOAD but you can in SQLPLUS. Please see below and tell me if it works...

paulb_at_OCP> CREATE OR REPLACE PACKAGE MY_PKG AS

  2          TYPE CURSOR_A IS REF CURSOR ;
  3          TYPE CURSOR_B IS REF CURSOR ;
  4          PROCEDURE SAMPLE_FUNC (CURSOR_A OUT SYS_REFCURSOR,

CURSOR_B OUT SYS_REFCURSOR, lastName
 VARCHAR);
  5 END MY_PKG;
  6 /

Package created.

paulb_at_OCP>
paulb_at_OCP> CREATE OR REPLACE PACKAGE BODY MY_PKG AS   2 PROCEDURE SAMPLE_FUNC (CURSOR_A OUT SYS_REFCURSOR, CURSOR_B OUT SYS_REFCURSOR, lastName
 VARCHAR)

  3          IS
  4          BEGIN

  5
  6

  7               OPEN  CURSOR_A FOR
  8               SELECT * from EMP WHERE ENAME = lastName;

  9
 10

 11               OPEN  CURSOR_B FOR
 12               SELECT * from DEPT;

 13
 14
 15 END SAMPLE_FUNC;
 16 END MY_PKG;
 17 /

Package body created.

paulb_at_OCP>
paulb_at_OCP>
paulb_at_OCP>
paulb_at_OCP> variable rc1 refcursor;
paulb_at_OCP> variable rc2 refcursor;
paulb_at_OCP>
paulb_at_OCP> execute MY_PKG.SAMPLE_FUNC(:rc1,:rc2,'ALLEN');

PL/SQL procedure successfully completed.

paulb_at_OCP>
paulb_at_OCP> print rc1

     EMPNO ENAME      JOB              MGR HIREDATE         SAL
COMM     DEPTNO

---------- ---------- --------- ---------- --------- ----------

• ---------- 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

paulb_at_OCP>
paulb_at_OCP> print rc2

    DEPTNO DNAME LOC
---------- -------------- -------------

        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

paulb_at_OCP>
paulb_at_OCP> Received on Fri Mar 10 2006 - 18:31:38 CST