REF CURSOR in PL/SQL

REF CURSOR in PL/SQL — A Complete Explanation

When working with Oracle PL/SQL, you often need to fetch and return multiple rows from the database. While normal cursors handle static queries well, they fall short when queries need to be dynamic and reusable. This is where REF CURSOR comes into play.

What is a REF CURSOR?

A REF CURSOR (Reference Cursor) is a pointer (reference) to a SQL result set. Instead of holding data itself, it points to a query’s output, allowing the result set to be passed between procedures, functions, and external applications.

In simple words:

REF CURSOR = A handle to a result set returned at runtime

Why REF CURSOR Was Introduced

Traditional cursors in PL/SQL:

• Are tightly coupled with SQL queries

• Cannot be passed easily between program units

• Are unsuitable for dynamic reporting or APIs

REF CURSOR solves these limitations by enabling:

• Runtime SQL execution

• Loose coupling between data logic and consumers

• Reusable result sets

How REF CURSOR Works (Conceptually)

1. Define a REF CURSOR type
2. Open it using a SQL query
3. Pass it to another program unit (optional)
4. Fetch rows from it
5. Close it after use

Types of REF CURSOR

1️⃣ Strong REF CURSOR

• Query structure is fixed

• Return type must be known at compile time

• Provides type safety

TYPE emp_cur IS REF CURSOR RETURN employees%ROWTYPE;

✔ Best when structure is predictable ❌ Less flexible

2️⃣ Weak REF CURSOR

• No predefined return type

• Query decided at runtime

• Most commonly used in real applications

TYPE emp_cur IS REF CURSOR;

✔ Highly flexible ❌ No compile-time structure validation

🧪 Example: Using a REF CURSOR

DECLARE
    TYPE emp_cur IS REF CURSOR;
    c_emp emp_cur;
    v_name employees.first_name%TYPE;
BEGIN
    OPEN c_emp FOR
        SELECT first_name FROM employees;

    LOOP
        FETCH c_emp INTO v_name;
        EXIT WHEN c_emp%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_name);
    END LOOP;

    CLOSE c_emp;
END;

🔹 The query is supplied at runtime 🔹 Data is fetched row-by-row 🔹 Cursor behaves like a movable pointer

🔗 Passing REF CURSOR Between Procedures

One of the biggest advantages of REF CURSOR is that it can be passed as a parameter.

CREATE OR REPLACE PROCEDURE get_employees (
    p_cursor OUT SYS_REFCURSOR
) AS
BEGIN
    OPEN p_cursor FOR
        SELECT employee_id, first_name FROM employees;
END;

✔ Common in reporting tools ✔ Widely used in Java, .NET, Python integrations

REF CURSOR vs Normal Cursor

| ----------------- | ------------- | ------------- |
| Query Defined     | Compile time  | Runtime       |
| Flexibility       | Low           | High          |
| Pass as Parameter | ❌ No          | ✅ Yes         |
| Use in APIs       | ❌ Rare        | ✅ Very common |
| Dynamic SQL       | ❌ No          | ✅ Yes         |

🌍 Real-World Use Cases

• REST APIs returning database records

• BI & reporting tools

• Dynamic search screens

• PL/SQL packages for data access layers

• Integration with Java / Python / APEX

Interview Tip

REF CURSOR does not store data — it stores a reference to the result set.

This one line can instantly impress interviewers 😉

Final Thoughts

If cursor is like a container, then REF CURSOR is like a remote control to that container.

• Mastering REF CURSOR is essential for:

• Advanced PL/SQL development

• Real-world enterprise applications

• Clean, reusable database APIs