Home » SQL & PL/SQL » SQL & PL/SQL » anonymous block for procedure-ref cursor out variable
anonymous block for procedure-ref cursor out variable [message #187124] Fri, 11 August 2006 00:44 Go to next message
bhagat.singh-j
Messages: 39
Registered: April 2006
Member
I've a procedure with a ref cursor type output variable.
Can someone help me with a anonymous block to invoke my procedure P_RET_REF_CURSOR and view each record of DEPARTMENT table

SQL> DESC DEPARTMENT
Name Null? Type
-------------------
DEPTNO NUMBER
ENAME VARCHAR2(200)


CREATE OR REPLACE PACKAGE l_ref_cursor
AS
TYPE rc IS REF CURSOR;
END;

CREATE OR REPLACE PROCEDURE P_RET_REF_CURSOR(lrc OUT l_ref_cursor.rc)
AS
BEGIN
OPEN lrc FOR 'SELECT * FROM DEPARTMENT';
END;

Thanks,
Bhags
Re: anonymous block for procedure-ref cursor out variable [message #187132 is a reply to message #187124] Fri, 11 August 2006 01:39 Go to previous messageGo to next message
bhagat.singh-j
Messages: 39
Registered: April 2006
Member
Got the answer.

from SQL plus do...

SQL> var rc refcursor;

SQL> exec p_ret_ref_cursor(:rc)

PL/SQL procedure successfully completed.

SQL> print :rc

DEPTNO ENAME
---------- -----------------------------------------------------
20 SMITH
20 SMITH
30 ALLEN
30 WARD
20 JONES
30 MARTIN
30 BLAKE
10 CLARK
20 SCOTT
10 KING
30 TURNER
20 ADAMS
30 JAMES
20 FORD
10 MILLER

15 rows selected.
Re: anonymous block for procedure-ref cursor out variable [message #187134 is a reply to message #187132] Fri, 11 August 2006 01:44 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Thanks for the feedback. I wish they all updated their threads like this.

MHE
Previous Topic: How do I convert an array in a string into rows?
Next Topic: HOWTO: Column name for a SELECT obtained from an inner SELECT.
Goto Forum:
  


Current Time: Thu Dec 08 18:34:30 CST 2016

Total time taken to generate the page: 0.20969 seconds