REF Cursors

A REF CURSOR is a cursor variable — a pointer to a query result set that can be passed between PL/SQL subprograms and returned to clients (Java, Python, .NET). Unlike static cursors, a REF CURSOR's query is determined at runtime.

Strong vs Weak REF CURSOR

A strong REF CURSOR has a fixed return type; a weak one does not:

-- Strong: tied to employees%ROWTYPE — type-checked at compile time
TYPE t_emp_cursor IS REF CURSOR RETURN employees%ROWTYPE;

-- Weak: accepts any query — checked at runtime only
TYPE t_generic_cursor IS REF CURSOR;

-- SYS_REFCURSOR: Oracle's built-in weak REF CURSOR type (no declaration needed)

Basic REF CURSOR Usage

DECLARE
    v_cursor   SYS_REFCURSOR;
    v_fname    employees.first_name%TYPE;
    v_lname    employees.last_name%TYPE;
    v_salary   employees.salary%TYPE;
BEGIN
    -- Open the cursor for a specific query
    OPEN v_cursor FOR
        SELECT first_name, last_name, salary
        FROM   employees
        WHERE  department_id = 90
        ORDER BY salary DESC;

    LOOP
        FETCH v_cursor INTO v_fname, v_lname, v_salary;
        EXIT WHEN v_cursor%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_fname || ' ' || v_lname || ': $' || v_salary);
    END LOOP;

    CLOSE v_cursor;
END;
/

Returning a REF CURSOR from a Procedure

The most common pattern: a procedure opens a cursor that the caller (or a client app) reads:

CREATE OR REPLACE PROCEDURE get_employees_by_dept(
    p_dept_id  IN  employees.department_id%TYPE,
    p_cursor   OUT SYS_REFCURSOR
) IS
BEGIN
    OPEN p_cursor FOR
        SELECT e.employee_id,
               e.first_name || ' ' || e.last_name AS full_name,
               e.salary,
               d.department_name
        FROM   employees   e
        JOIN   departments d ON d.department_id = e.department_id
        WHERE  e.department_id = p_dept_id
        ORDER BY e.salary DESC;
END get_employees_by_dept;
/

Calling from PL/SQL:

DECLARE
    v_cur      SYS_REFCURSOR;
    v_emp_id   employees.employee_id%TYPE;
    v_name     VARCHAR2(100);
    v_salary   employees.salary%TYPE;
    v_dept     departments.department_name%TYPE;
BEGIN
    get_employees_by_dept(80, v_cur);

    LOOP
        FETCH v_cur INTO v_emp_id, v_name, v_salary, v_dept;
        EXIT WHEN v_cur%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_name || ' ($' || v_salary || ')');
    END LOOP;

    CLOSE v_cur;
END;
/

Returning a REF CURSOR from a Function

Functions can return SYS_REFCURSOR and be called from SQL:

CREATE OR REPLACE FUNCTION get_top_earners(p_n IN NUMBER DEFAULT 5)
RETURN SYS_REFCURSOR IS
    v_cursor SYS_REFCURSOR;
BEGIN
    OPEN v_cursor FOR
        SELECT employee_id,
               first_name || ' ' || last_name AS full_name,
               salary
        FROM   employees
        ORDER BY salary DESC
        FETCH FIRST p_n ROWS ONLY;

    RETURN v_cursor;
END get_top_earners;
/

-- Consume in PL/SQL
DECLARE
    v_cur     SYS_REFCURSOR := get_top_earners(3);
    v_id      NUMBER;
    v_name    VARCHAR2(100);
    v_salary  NUMBER;
BEGIN
    LOOP
        FETCH v_cur INTO v_id, v_name, v_salary;
        EXIT WHEN v_cur%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_name || ': $' || v_salary);
    END LOOP;
    CLOSE v_cur;
END;
/

Opening with Dynamic SQL

A REF CURSOR query can be built at runtime — combining the flexibility of dynamic SQL with the clean cursor interface:

CREATE OR REPLACE PROCEDURE search_employees(
    p_column   IN  VARCHAR2,
    p_value    IN  VARCHAR2,
    p_cursor   OUT SYS_REFCURSOR
) IS
    v_sql  VARCHAR2(500);
BEGIN
    -- Build query dynamically — note bind variable :val to prevent injection
    v_sql := 'SELECT employee_id, first_name, last_name, salary
              FROM   employees
              WHERE  ' || DBMS_ASSERT.SIMPLE_SQL_NAME(p_column) || ' = :val
              ORDER BY employee_id';

    OPEN p_cursor FOR v_sql USING p_value;
END search_employees;
/

-- Test
DECLARE
    v_cur   SYS_REFCURSOR;
    v_id    NUMBER;
    v_fn    VARCHAR2(50);
    v_ln    VARCHAR2(50);
    v_sal   NUMBER;
BEGIN
    search_employees('JOB_ID', 'IT_PROG', v_cur);
    LOOP
        FETCH v_cur INTO v_id, v_fn, v_ln, v_sal;
        EXIT WHEN v_cur%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_id || ': ' || v_fn || ' ' || v_ln);
    END LOOP;
    CLOSE v_cur;
END;
/

Passing REF CURSORs to Java / JDBC

In Java, a REF CURSOR OUT parameter maps to java.sql.ResultSet:

// Java example (not PL/SQL — shown for context)
CallableStatement cs = conn.prepareCall("{call get_employees_by_dept(?, ?)}");
cs.setInt(1, 80);
cs.registerOutParameter(2, OracleTypes.CURSOR);
cs.execute();
ResultSet rs = (ResultSet) cs.getObject(2);
while (rs.next()) {
    System.out.println(rs.getString("full_name") + ": $" + rs.getDouble("salary"));
}
rs.close();

Strong REF CURSOR with %ROWTYPE

Use a strong cursor when the return type is always the same — Oracle validates column types at compile time:

DECLARE
    TYPE t_emp_cur IS REF CURSOR RETURN employees%ROWTYPE;
    v_cursor  t_emp_cur;
    v_emp     employees%ROWTYPE;
BEGIN
    OPEN v_cursor FOR
        SELECT * FROM employees WHERE department_id = 60;

    LOOP
        FETCH v_cursor INTO v_emp;
        EXIT WHEN v_cursor%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_emp.first_name || ': $' || v_emp.salary);
    END LOOP;

    CLOSE v_cursor;
END;
/

Summary

• A REF CURSOR is a cursor variable — a runtime pointer to a query result set.
• Strong REF CURSORs have a fixed return type (compile-time safety). Weak ones accept any query.
• SYS_REFCURSOR is Oracle's built-in weak REF CURSOR — no type declaration needed.
• Pass REF CURSORs as OUT parameters from procedures, or RETURN them from functions.
• Open with OPEN v_cursor FOR sql_string USING bind_vars for dynamic queries.
• Always CLOSE REF CURSORs when done — callers are responsible when the cursor is returned as an OUT parameter.