Oracle | Ref Cursors

Oracle Ref Cursors Version 21c

General Information

Library Note

Morgan's Library Page Header

Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.

Data Dictionary Objects

ALL_PROCEDURES	CDB_SOURCE	DBA_SOURCE_AE
ALL_SOURCE	CDB_SOURCE_AE	USER_PROCEDURES
ALL_SOURCE_AE	DBA_PROCEDURES	USER_SOURCE
CDB_PROCEDURES	DBA_SOURCE	USER_SOURCE_AE

System Privileges

CREATE ANY PROCEDURE

CREATE PROCEDURE

Strongly Typed

Note: A REF CURSOR that specifies a specific return type.

Package Head 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 FOR 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; /

Run 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 one using 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 FOR '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; /

Run Demo set serveroutput on exec parent(1) exec parent(17)

Passing Ref Cursors

Child Procedure

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, 'Hans Forbrich');

      INSERT INTO employees (empid, empname) VALUES (3, 'Caleb Small');

      COMMIT;

      

      
        
          CREATE OR REPLACE PROCEDURE pass_ref_cur(p_cursor SYS_REFCURSOR)

          AUTHID DEFINER 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 FOR

        'SELECT empname FROM employees';

      

        pass_ref_cur(rec_array);

        CLOSE rec_array;

      END;

      /

VARRAY Ref Cursors

Use a REF CURSOR to pass a VARRAY and disassemble it on the receiving end CREATE OR REPLACE TYPE Project AUTHID DEFINER AS OBJECT ( project_no NUMBER(2), title VARCHAR2(35), cost NUMBER(7,2)); / CREATE OR REPLACE TYPE ProjectList AS VARRAY(50) OF Project; / CREATE TABLE department ( dept_id NUMBER(2), dname VARCHAR2(15), budget NUMBER(11,2), projects ProjectList); CREATE OR REPLACE PROCEDURE varray_refcur(outparm OUT NOCOPY SYS_REFCURSOR) AUTHID DEFINER IS BEGIN OPEN outparm FOR SELECT * FROM department; END varray_refcur; / INSERT INTO department VALUES(30, 'Accounting', 1205700, ProjectList (Project(1, 'Design New Expense Report', 3250), Project(2, 'Outsource Payroll', 12350), Project(3, 'Evaluate Merger Proposal', 2750), Project(4, 'Audit Accounts Payable', 1425))); INSERT INTO department VALUES(50, 'Maintenance', 925300, ProjectList (Project(1, 'Repair Leak in Roof', 2850), Project(2, 'Install New Door Locks', 1700), Project(3, 'Wash Front Windows', 975), Project(4, 'Repair Faulty Wiring', 1350), Project(5, 'Winterize Cooling System', 1125))); INSERT INTO department VALUES(60, 'Security', 750400, ProjectList (Project(1, 'Issue New Employee Badges', 13500), Project(2, 'Find Missing IC Chips', 2750), Project(3, 'Upgrade Alarm System', 3350), Project(4, 'Inspect Emergency Exits', 1900))); COMMIT; set serveroutput on DECLARE drow SYS_REFCURSOR; drec department%ROWTYPE; BEGIN -- call the proc that returns the weakly typed ref cursor varray_refcur(drow); FOR i IN 1 .. 3 LOOP FETCH drow INTO drec; dbms_output.put_line('Dept#: ' || drec.dept_id); dbms_output.put_line('DName: ' || drec.dname); dbms_output.put_line('Budget:' || drec.budget); dbms_output.put_line('Proj#: ' || drec.projects(i).project_no); dbms_output.put_line('Title: ' || drec.projects(i).title); dbms_output.put_line('PCost: ' || drec.projects(i).cost); dbms_output.put_line('------------------------------'); END LOOP; END; /