Ref Cursors

PL/SQL REF Cursors: A Comprehensive Guide

In PL/SQL, a standard cursor is static; it is tied to a specific SELECT statement at compile time. A REF CURSOR (Reference Cursor) is a dynamic cursor variable. It is essentially a pointer to a result set that can be opened for different queries at runtime (Oracle, 2009). This makes REF Cursors incredibly powerful for returning result sets from stored procedures to external applications, such as Java, .NET, or Oracle APEX (Oracle, 2026).

---

Part 1: Using REF Cursors (The Core Concept)

The most common use case for a REF Cursor is as an OUT parameter in a stored procedure. The procedure opens the cursor and passes it back to the calling application (Oracle, 2026).

Crucial Rule: When a procedure returns a SYS_REFCURSOR, it should NOT close the cursor. It is up to the calling code (the application or the PL/SQL block that called the procedure) to fetch the data and close the cursor (Oracle, 2026).

The Procedure Example

The following procedure opens a query using a SYS_REFCURSOR output parameter to return a subset of records from the EMP table.

CREATE OR REPLACE
PROCEDURE get_emp_rs (
    p_deptno    IN  emp.deptno%TYPE,
    p_recordset OUT SYS_REFCURSOR
) AS 
BEGIN 
  OPEN p_recordset FOR
    SELECT ename,
           empno,
           deptno
    FROM   emp
    WHERE  deptno = p_deptno
    ORDER BY ename;
END get_emp_rs;
/

---

Part 2: How to Call the Procedure (The Missing Piece)

Because the procedure above does not close the cursor, you must write a PL/SQL block to call it, fetch the data, and close it. Here is how you would call get_emp_rs from another PL/SQL block (Oracle, 2009).

SET SERVEROUTPUT ON
DECLARE
    -- 1. Declare a variable of type SYS_REFCURSOR
    v_cursor SYS_REFCURSOR;
    
    -- 2. Declare variables to hold the fetched columns
    v_ename  emp.ename%TYPE;
    v_empno  emp.empno%TYPE;
    v_deptno emp.deptno%TYPE;
BEGIN
    -- 3. Call the procedure and pass the cursor variable
    get_emp_rs(p_deptno => 10, p_recordset => v_cursor);
    
    -- 4. Fetch the data in a loop
    LOOP
        FETCH v_cursor INTO v_ename, v_empno, v_deptno;
        EXIT WHEN v_cursor%NOTFOUND;
        
        DBMS_OUTPUT.PUT_LINE('Name: ' || v_ename || ' | ID: ' || v_empno);
    END LOOP;
    
    -- 5. Close the cursor (The calling code is responsible for this)
    CLOSE v_cursor;
END;
/

---

Part 3: Strongly Typed vs. Weakly Typed REF Cursors

When defining a REF Cursor, you have two choices: Strongly Typed and Weakly Typed (Oracle, 2009).

1. Strongly Typed REF Cursor

A strongly typed REF Cursor has a fixed return type. Oracle knows exactly what columns the cursor will return at compile time. This provides compile-time safety, as the compiler will reject queries that do not match the declared return type (Oracle, 2009).

DECLARE
    -- The cursor MUST return columns that match the EMP table
    TYPE emp_cursor IS REF CURSOR RETURN emp%ROWTYPE;
    v_cur emp_cursor;
BEGIN
    OPEN v_cur FOR SELECT * FROM emp WHERE deptno = 10;
END;
/

2. Weakly Typed REF Cursor

A weakly typed REF Cursor has no fixed return type. You can open it for any query you want. SYS_REFCURSOR is a built-in weakly typed REF Cursor (Oracle, 2009).

DECLARE
    -- This can be opened for ANY select statement
    v_cur SYS_REFCURSOR; 
BEGIN
    OPEN v_cur FOR SELECT ename, salary FROM emp;
    -- ... later ...
    OPEN v_cur FOR SELECT department_id, department_name FROM departments;
END;
/

---

Part 4: Why Use REF Cursors? (Real-World Benefits)

• Returning Result Sets to Applications: In Oracle APEX, Java, or .NET, you cannot easily return a standard PL/SQL cursor. You must use a SYS_REFCURSOR to pass the data back to the application's UI (Oracle, 2026).
• Dynamic SQL: Because you can open a REF Cursor for different queries at runtime, they are perfect for building dynamic search screens where the user selects different filters (Oracle, 2009).
• Modularity: You can create generic procedures that return a REF Cursor, and then open that same cursor for different queries based on business logic (Oracle, 2009).

Conclusion: REF Cursors are the bridge between PL/SQL and the outside world. Mastering SYS_REFCURSOR is essential for any Oracle developer building web applications or APIs. Just remember the golden rule: if the procedure opens it, the caller must close it (Oracle, 2026).

---

References

Oracle. (2009). PL/SQL language elements: Cursor variables. Oracle Help Center. https://docs.oracle.com/cd/A91034_01/DOC/appdev.901/a89856/13_elems13.htm

Oracle. (2026). Database PL/SQL language reference: Cursor variables (19c). Oracle Help Center. https://docs.oracle.com/en/database/oracle/oracle-database/19/lnpls/cursor-variables.html