REF CURSORs and SYS_REFCURSOR: Flexible Result Sets in PL/SQL

What Are REF CURSORs

A REF CURSOR is a PL/SQL variable that holds a pointer to a query result set. Unlike regular cursors which are tied to a specific SQL statement at compile time, REF CURSORs can point to different queries at runtime and can be passed between procedures as parameters. They are the primary mechanism for returning query results from PL/SQL to calling applications.

SYS_REFCURSOR: The Universal REF CURSOR

Oracle provides a predefined weak REF CURSOR type called SYS_REFCURSOR that can point to any query regardless of its column structure. This eliminates the need to declare custom REF CURSOR types in most situations:

CREATE OR REPLACE PROCEDURE get_department_employees(
    p_dept_id    IN  NUMBER,
    p_cursor     OUT SYS_REFCURSOR
)
IS
BEGIN
    OPEN p_cursor FOR
        SELECT employee_id, first_name, last_name, salary, hire_date
          FROM employees
         WHERE department_id = p_dept_id
         ORDER BY last_name;
END;
/

-- Calling the procedure
DECLARE
    l_cursor   SYS_REFCURSOR;
    l_emp_id   NUMBER;
    l_fname    VARCHAR2(50);
    l_lname    VARCHAR2(50);
BEGIN
    get_department_employees(20, l_cursor);
    LOOP
        FETCH l_cursor INTO l_emp_id, l_fname, l_lname;
        EXIT WHEN l_cursor%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(l_fname || ' ' || l_lname);
    END LOOP;
    CLOSE l_cursor;
END;

Dynamic Queries With REF CURSORs

REF CURSORs shine when the query needs to change at runtime:

CREATE OR REPLACE PROCEDURE search_employees(
    p_search_by IN  VARCHAR2,
    p_value     IN  VARCHAR2,
    p_cursor    OUT SYS_REFCURSOR
)
IS
BEGIN
    IF p_search_by = 'NAME' THEN
        OPEN p_cursor FOR
            SELECT employee_id, first_name, last_name
              FROM employees
             WHERE UPPER(last_name) LIKE UPPER(p_value) || '%';
    ELSIF p_search_by = 'DEPT' THEN
        OPEN p_cursor FOR
            SELECT employee_id, first_name, last_name
              FROM employees
             WHERE department_id = TO_NUMBER(p_value);
    END IF;
END;

REF CURSORs in APEX

APEX can consume REF CURSORs as report sources through the “PL/SQL Function Body Returning SQL Query” source type, or by calling a procedure that returns a SYS_REFCURSOR and piping the results through a pipelined function. However, for most APEX use cases, a simple SQL query or a PL/SQL function returning a SQL string is simpler and more performant than REF CURSORs.

Important Rules

Always close REF CURSORs when done to release resources. Do not fetch from a closed cursor. Do not close a cursor that has already been closed. In Oracle 12c and later, implicit statement results using DBMS_SQL.RETURN_RESULT provide an alternative way to return result sets that is often simpler than OUT parameter REF CURSORs.