Oracle REF CURSORS
Versions 7.3 - 19.3

Security Advisory
REF CURSORS in Oracle make it possible to define cursors that can be passed as parameters and to define cursors using dynamic SQL. REF CURSORS are powerful tools that belong in the toolbox of every PL/SQL Developer. But with that strong endorsement comes the need for a health dose of preventative medicine: Weakly Typed REF CURSORS open up all of the same security concerns as does the DBMS_SQL package and Native Dynamic SQL.
 
Recommended Security Rules

 NEVER
  • Use Weakly Typed Ref Cursors without using the DBMS_ASSERT package to sanitize the inputs.
 WITH GREAT CARE
  • Test any code using Weakly Typed REF CURSORS to assure that a SQL Injection attack is not possible.
 CAUTIONS
  • In addition to the use of DBMS_ASSERT use Bind Variables whenever possible.
  • Do not rely on the expertise of anyone that does not know how to craft and deploy a SQL Injection attack to validate the code.
 
How Oracle Works
There are two different types of REF CURSORS, strongly typed which are not dynamic and weakly typed that are. Here are examples of each.

After this section everything on this page will focus solely on the Weakly Typed version as they are the ones that introduce concerns about SQL Injection and the inability, as with Native Dynamic SQL, to identify objects they reference using the DBA_DEPENDENCIES view.

From the security standpoint treat Weakly Typed REF CURSORS as just another form of Native Dynamic SQL. One that has shares both its strengths and its security issues.
Strongly Typed REF CURSOR

The REF CURSOR is the OUT parameter of the procedure "CHILD"
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;
/

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

set serveroutput on

exec strongly_typed.parent(1)
exec strongly_typed.parent(8)
Weakly Typed REF CURSOR

In this example the REF CURSOR is defined as
p_retcur SYS_REFCURSOR.

Using SYS_REFCURSOR is not required but it is the most commonly used technique.
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;
/

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

set serveroutput on

exec parent(1)
exec parent(17)
Weakly Typed REF CURSOR

In this example SYS_REFCURSOR is not used and the cursor is defined by string concatenation
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;
/
 
REF CURSOR Demos
REF CURSOR as the OUT parameter of a stored procedure

The REF CURSOR in this demo is defined in two paces. First in the input parameter of the procedure pass_ref_cur and also in the declaration section of the anonymous block that calls pass_ref_cur.

The name of the table, employees, is defined within a string that could be created dynamically, perhaps creating the SQL statement as the result of parameters passed resulting in SQL that has never been tested being executed for the first time in production..

Code like this demands the use of DBMS_ASSERT, demands that inputs be sanitized, demands well crafted exception handling, and hides object dependencies.
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 IN 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

This example demonstrates 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
DBMS_ASSERT
DBMS_SQL
DBMS_SYS_SQL
EDITION BASED REDEFINITION
Native Dynamic SQL