Oracle ref cursor reference

Oracle ref cursors

Version 10.2:

Stronugly Typed Note: A ref cursor that specifies a specific return type. Package Header Create or replace package strongly_typed is TYPE return_cur is ref cursor return all_tables % ROWTYPE; PROCEDURE child (p_return_rec OUT return_cur ); PROCEDURE parent (p_NumRecs PLS_INTEGER ); END strongly_typed; / Package body Create or replace package body strongly_typed is PROCEDURE child (p_return_rec OUT return_cur) IS BEGIN OPEN p_return_rec SELECT * FROM all_tables; END child; -- ===================================================== ============== PROCEDURE parent (p_NumRecs PLS_INTEGER) IS P_retcur return_cur; At_rec all_tables % ROWTYPE; BEGIN Child (p_retcur ); FOR I IN 1 .. p_NumRecs LOOP FETCH p_retcur INTO at_rec; Dbms_output.put_line (at_rec.table_name | '-' | At_rec.tablespace_name | '-' | TO_CHAR (at_rec.initial_extent) | '-' | TO_CHAR (at_rec.next_extent )); End loop; END parent; END strongly_typed; / To run the demo Set serveroutput on Exec strongly_typed.parent (1) Exec strongly_typed.parent (8) Weakly Typed Note: A ref cursor that does not specify the return type such as sys_refcursor. Child procedure Create or replace procedure child ( P_numrecs in pls_integer, P_return_cur out sys_refcursor) Is BEGIN OPEN p_return_cur 'Select * FROM all_tables WHERE rownum <= '| p_NumRecs; END child; / Parent procedure Create or replace procedure parent (pnumrecs varchar2) is P_retcur sys_refcursor; At_rec all_tables % rowtype; Begin Child (pnumrecs, p_retcur ); FOR I IN 1 .. pNumRecs LOOP FETCH p_retcur INTO at_rec; Dbms_output.put_line (at_rec.table_name | '-' | At_rec.tablespace_name | '-' | TO_CHAR (at_rec.initial_extent) | '-' | TO_CHAR (at_rec.next_extent )); End loop; END parent; / To run the demo Set serveroutput on Exec parent (1) Exec parent (17) Passing Ref Cursors Ref Cursor Passing Demo Create table employees ( Empid NUMBER (5 ), Empname VARCHAR2 (30 )); Insert into employees (empid, empname) VALUES (1, 'Dan Morgan '); Insert into employees (empid, empname) VALUES (2, 'Jack cline '); Insert into employees (empid, empname) VALUES (3, 'caleb small '); COMMIT; Create or replace procedure pass_ref_cur (p_cursor SYS_REFCURSOR) IS TYPE array_t is table of VARCHAR2 (4000) Index by BINARY_INTEGER; Rec_array array_t; BEGIN FETCH p_cursor bulk collect into rec_array; FOR I IN rec_array.FIRST .. rec_array.LAST LOOP Dbms_output.put_line (rec_array (I )); End loop; END pass_ref_cur; / Set serveroutput on DECLARE Rec_array SYS_REFCURSOR; BEGIN OPEN rec_array 'Select empname FROM ployees '; Pass_ref_cur (rec_array ); CLOSE rec_array; END; /