REF CURSOR FAQS

1. What is a REF CURSOR in Oracle?

A REF CURSOR is a cursor variable that holds the reference (pointer) to a query result set.

Unlike normal explicit cursors:

·        REF CURSOR does not contain a query itself.

·        It is opened for a query dynamically.

·        It can be passed between procedures and returned to client applications.

2. Why do we use REF CURSOR?

We use REF CURSOR when:

·        Returning result sets from procedures

·        Passing query results between subprograms

·        Working with client applications (Java, .NET, etc.)

·        Executing dynamic SQL

·        When query structure may vary

3. What are the types of REF CURSOR?

There are two types:

1️⃣ Strong REF CURSOR

·        Return type is predefined

·        Must match the query structure

2️⃣ Weak REF CURSOR

·        No predefined return type

·        Can return any query result

4. What is a Strong REF CURSOR?

A strongly typed REF CURSOR specifies the row structure.

Example:

DECLARE

   TYPE emp_ref_type IS REF CURSOR

      RETURN employees%ROWTYPE;

   emp_ref emp_ref_type;

   emp_record employees%ROWTYPE;

BEGIN

   OPEN emp_ref FOR

      SELECT * FROM employees WHERE department_id = 10;

   LOOP

      FETCH emp_ref INTO emp_record;

      EXIT WHEN emp_ref%NOTFOUND;

      DBMS_OUTPUT.PUT_LINE(emp_record.first_name);

   END LOOP;

   CLOSE emp_ref;

END;

✔ Ensures compile-time type checking.

5. What is a Weak REF CURSOR?

A weak REF CURSOR does not define a return type.

Example:

DECLARE

   TYPE generic_ref IS REF CURSOR;

   emp_ref generic_ref;

   v_id employees.employee_id%TYPE;

   v_name employees.first_name%TYPE;

BEGIN

   OPEN emp_ref FOR

      SELECT employee_id, first_name

      FROM employees

      WHERE department_id = 20;

   LOOP

      FETCH emp_ref INTO v_id, v_name;

      EXIT WHEN emp_ref%NOTFOUND;

      DBMS_OUTPUT.PUT_LINE(v_name);

   END LOOP;

   CLOSE emp_ref;

END;

✔ More flexible
❌ No compile-time validation

6. What is SYS_REFCURSOR?

SYS_REFCURSOR is a predefined weak REF CURSOR provided by Oracle.

It avoids defining your own type.

Example:

DECLARE

   emp_ref SYS_REFCURSOR;

   v_name employees.first_name%TYPE;

BEGIN

   OPEN emp_ref FOR

      SELECT first_name FROM employees;

   LOOP

      FETCH emp_ref INTO v_name;

      EXIT WHEN emp_ref%NOTFOUND;

      DBMS_OUTPUT.PUT_LINE(v_name);

   END LOOP;

   CLOSE emp_ref;

END;

✔ Most commonly used in real projects.

7. How do you return REF CURSOR from a procedure?

Example:

CREATE OR REPLACE PROCEDURE get_employees (

   p_dept_id IN NUMBER,

   p_recordset OUT SYS_REFCURSOR

)

AS

BEGIN

   OPEN p_recordset FOR

      SELECT employee_id, first_name

      FROM employees

      WHERE department_id = p_dept_id;

END;

Used by client applications to fetch records.

8. Difference between Explicit Cursor and REF CURSOR

Feature	Explicit Cursor	REF CURSOR
Query location	Defined in declaration	Assigned at OPEN time
Can be returned	No	Yes
Can be passed as parameter	No	Yes
Dynamic SQL support	Limited	Yes
Use case	Local row processing	Result set returning

9. What are advantages of REF CURSOR?

✔ Return result sets from procedures
✔ Support dynamic SQL
✔ Can be passed between programs
✔ Useful for multi-tier applications
✔ Flexible result structure (weak type)

10. What are disadvantages?

·        Must manually close

·        Weak REF CURSOR lacks compile-time checking

·        Slightly more complex than explicit cursor

11. Can REF CURSOR improve performance?

Not directly.

It is mainly used for:

·        Flexibility

·        Client communication

·        Clean architecture

For performance with large data:

·        Use BULK COLLECT

·        Use FORALL

12. When should you use Strong vs Weak REF CURSOR?

Use Strong	Use Weak
Structure known	Structure varies
Need type safety	Need flexibility
Inside PL/SQL only	For client applications

13. Common mistakes with REF CURSOR

·        Forgetting to CLOSE

·        Fetching into wrong variable types

·        Not handling empty result sets

·        Not passing OUT parameter properly

Interview Tip

If interviewer asks:

“Why is SYS_REFCURSOR commonly used?”

Answer:

·        Built-in type

·        No need to define custom type

·        Flexible

·        Simplifies procedure design

·        Ideal for returning result sets