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;
/

Related Topics
Built-in Functions
Built-in Packages
Database Security
Native Dynamic SQL
Procedures
What's New In 19c
What's New In 20c-21c

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-2019 Daniel A. Morgan All Rights Reserved
  DBSecWorx