Oracle Ref Cursors
Version 12.1.0.1

General Information
Library Note This page has been updated for Oracle release 12cR1 and shown to be compatible in the Container DB.
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) 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
How to 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;
/

Related Topics
Native Dynamic SQL
Packages
Procedures

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved