Oracle PL/SQL for DBAs

The Case Against SYS_REFCURSOR

Oracle’s SYS_REFCURSOR function provides a way to quickly declare a weakly typed REF cursor that can process almost any cursor. The fact that SYS_REFCURSOR can be specified as the datatype means that any old SELECT statement can be passed in, as long as the cursor isn’t actually manipulated in the function. For example, this function is wide open to any SELECT.

    CREATE OR REPLACE FUNCTION wide_open ( p_curs SYS_REFCURSOR )
                      RETURN number_t IS
      v_ret_val number_t := number_t(  );
    BEGIN
      v_ret_val.EXTEND;
      v_ret_val(v_ret_val.LAST) := 99;
      RETURN v_ret_val;
    END;

Any valid SELECT will work.

    SQL> SELECT *
      2    FROM TABLE(wide_open(CURSOR(SELECT NULL
      3                                  FROM DUAL)));

    COLUMN_VALUE
    ------------
              99

    SQL> SELECT *
      2    FROM TABLE(wide_open(CURSOR(SELECT *
      3                                  FROM orders)));

    COLUMN_VALUE
    ------------
              99

But things have to become more isolated because, presumably, records will be fetched within the function. Thus, local variables are required to fetch into.

    CREATE OR REPLACE FUNCTION wide_open ( p_curs SYS_REFCURSOR )
                      RETURN number_t IS
      v_ret_val   number_t := number_t(  );
      v_order_rec orders%ROWTYPE;
    BEGIN
      FETCH p_curs INTO v_order_rec;
      v_ret_val.EXTEND;
      v_ret_val(v_ret_val.LAST) := 99;
      RETURN v_ret_val;
    END;

Only SELECT statements getting all columns from the ORDERS table can now be passed to this function. Others will raise the ORA-01007 error .

    SQL> SELECT *
      2    FROM TABLE(wide_open(CURSOR(SELECT NULL
      3                                  FROM DUAL))); FROM TABLE(wide_open(CURSOR(SELECT NULL * ERROR at line 2: ORA-01007: ...