Oracle REF CURSOR Faqs With Examples

1. What is a REF CURSOR?

A REF CURSOR is a cursor variable that holds a reference to a query's result set.

DECLARE
    TYPE ref_cursor_type IS REF CURSOR;
    v_cursor ref_cursor_type;
BEGIN
    OPEN v_cursor FOR
        SELECT employee_id, first_name, salary
        FROM employees;


-- Process result set

CLOSE v_cursor;


END;
/

2. Why is it called REF CURSOR?

"REF" means reference. A REF CURSOR variable doesn't itself contain the rows. It holds a reference to the result set produced by a query.

REF CURSOR variable
        ↓
   Result set
        ↓
+-------+-------+
| Row 1 | Row 2 |
+-------+-------+

3. What is the syntax for a REF CURSOR?

Strong/basic form:

TYPE ref_cursor_type IS REF CURSOR;

v_cursor ref_cursor_type;

OPEN v_cursor FOR
SELECT ...;

4. What is a weak REF CURSOR?

A weak REF CURSOR does not specify the structure of the returned rows.

DECLARE
    TYPE ref_cursor_type IS REF CURSOR;
    v_cursor ref_cursor_type;
BEGIN
    OPEN v_cursor FOR
        SELECT employee_id, first_name, salary
        FROM employees;


CLOSE v_cursor;


END;
/

The cursor can point to result sets with different structures.

5. What is a strong REF CURSOR?

A strong REF CURSOR specifies the type of rows that the cursor can return.

DECLARE
    TYPE emp_ref_cursor IS REF CURSOR
        RETURN employees%ROWTYPE;


v_cursor emp_ref_cursor;


BEGIN
OPEN v_cursor FOR
SELECT *
FROM employees;


CLOSE v_cursor;


END;
/

Here the cursor is strongly typed to return a row matching employees%ROWTYPE.

6. Strong vs Weak REF CURSOR

Feature Strong REF CURSOR Weak REF CURSOR
Return type specified Yes No
Structure checked More strictly More flexible
Can point to different row structures Limited Yes
Compile-time checking Stronger Weaker
Flexibility Lower Higher

Easy memory trick:

Strong = Structure known
Weak   = Structure not fixed

7. What is SYS_REFCURSOR?

SYS_REFCURSOR is a predefined weak REF CURSOR type provided by Oracle. You don't need to declare your own REF CURSOR type.

DECLARE
    v_cursor SYS_REFCURSOR;
BEGIN
    OPEN v_cursor FOR
        SELECT employee_id, first_name, salary
        FROM employees;


CLOSE v_cursor;


END;
/

8. Why is SYS_REFCURSOR popular?

Because it is simple. Instead of declaring your own REF CURSOR type, you can simply write:

v_cursor SYS_REFCURSOR;

9. How do we OPEN a REF CURSOR?

Use:

OPEN cursor_variable FOR SELECT_statement;

Example:

DECLARE
    v_cursor SYS_REFCURSOR;
BEGIN
    OPEN v_cursor FOR
        SELECT employee_id, first_name
        FROM employees;


CLOSE v_cursor;


END;
/

Important: For a normal explicit cursor, use OPEN emp_cursor;. For a REF CURSOR, use OPEN v_cursor FOR SELECT ...;

10. Can we FETCH from a REF CURSOR?

Yes.

DECLARE
    v_cursor SYS_REFCURSOR;
    v_id     employees.employee_id%TYPE;
    v_name   employees.first_name%TYPE;
    v_salary employees.salary%TYPE;
BEGIN
    OPEN v_cursor FOR
        SELECT employee_id, first_name, salary
        FROM employees;


LOOP
    FETCH v_cursor
    INTO v_id, v_name, v_salary;

    EXIT WHEN v_cursor%NOTFOUND;

    DBMS_OUTPUT.PUT_LINE(
        v_id || ' - ' ||
        v_name || ' - ' ||
        v_salary
    );
END LOOP;

CLOSE v_cursor;


END;
/

11. Can we CLOSE a REF CURSOR?

Yes. A REF CURSOR should be closed after its result set has been processed when the current code owns the cursor lifecycle.

CLOSE v_cursor;

12. What happens if we forget to close a REF CURSOR?

The cursor remains open and can consume database resources. Therefore, after processing, CLOSE v_cursor; should normally be performed.

13. Can we OPEN a REF CURSOR for different queries?

Yes. This is one of its major advantages.

DECLARE
    v_cursor SYS_REFCURSOR;
BEGIN
    OPEN v_cursor FOR
        SELECT employee_id, first_name
        FROM employees;


CLOSE v_cursor;

OPEN v_cursor FOR
    SELECT department_id, department_name
    FROM departments;

CLOSE v_cursor;


END;
/

14. Can a REF CURSOR accept parameters?

A REF CURSOR itself does not have cursor parameters in the same way a parameterized explicit cursor does. Instead, you can use PL/SQL variables in the query.

DECLARE
    v_cursor  SYS_REFCURSOR;
    v_dept_id NUMBER := 10;
BEGIN
    OPEN v_cursor FOR
        SELECT employee_id, first_name, salary
        FROM employees
        WHERE department_id = v_dept_id;


CLOSE v_cursor;


END;
/

15. Can we use OPEN FOR with dynamic SQL?

Yes.

DECLARE
    v_cursor SYS_REFCURSOR;
    v_sql    VARCHAR2(1000);
BEGIN
    v_sql :=
        'SELECT employee_id, first_name
         FROM employees
         WHERE department_id = :1';


OPEN v_cursor FOR v_sql USING 10;

CLOSE v_cursor;


END;
/

Here v_sql is the dynamic SQL statement, :1 is the bind placeholder, and 10 is supplied through USING.

16. Why is REF CURSOR useful in procedures?

A procedure can return a result set to the caller using an OUT parameter.

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

The procedure doesn't return individual rows one by one. It returns a cursor pointing to the result set.

17. How do we call a procedure that returns a REF CURSOR?

DECLARE
    v_cursor SYS_REFCURSOR;
    v_id     employees.employee_id%TYPE;
    v_name   employees.first_name%TYPE;
    v_salary employees.salary%TYPE;
BEGIN
    get_employees(v_cursor);


LOOP
    FETCH v_cursor
    INTO v_id, v_name, v_salary;

    EXIT WHEN v_cursor%NOTFOUND;

    DBMS_OUTPUT.PUT_LINE(
        v_id || ' - ' ||
        v_name || ' - ' ||
        v_salary
    );
END LOOP;

CLOSE v_cursor;


END;
/

18. Can a function return a REF CURSOR?

Yes.

CREATE OR REPLACE FUNCTION get_employees
RETURN SYS_REFCURSOR
IS
    v_cursor SYS_REFCURSOR;
BEGIN
    OPEN v_cursor FOR
        SELECT employee_id,
               first_name,
               salary
        FROM employees;


RETURN v_cursor;


END;
/

19. Can a REF CURSOR be an OUT parameter?

Yes. This is very common.

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

20. Can a REF CURSOR be passed to another procedure?

Yes.

CREATE OR REPLACE PROCEDURE process_employees (
    p_cursor IN SYS_REFCURSOR
)
IS
    v_id   employees.employee_id%TYPE;
    v_name employees.first_name%TYPE;
BEGIN
    LOOP
        FETCH p_cursor INTO v_id, v_name;
        EXIT WHEN p_cursor%NOTFOUND;


    DBMS_OUTPUT.PUT_LINE(v_id || ' - ' || v_name);
END LOOP;


END;
/

21. Can a REF CURSOR be returned to an application?

Yes. This is one of its most important uses. A PL/SQL procedure can return a cursor, and an application can fetch the rows from that cursor.

22. REF CURSOR vs Explicit Cursor

Explicit Cursor REF CURSOR
Declared with a specific query Cursor variable
Query fixed in declaration Query supplied at OPEN FOR
Less flexible More flexible
Can be parameterized Uses variables or dynamic SQL at runtime

23. REF CURSOR vs Parameterized Cursor

Parameterized explicit cursor:

CURSOR emp_cursor(p_dept_id NUMBER) IS
    SELECT *
    FROM employees
    WHERE department_id = p_dept_id;

REF CURSOR:

v_cursor SYS_REFCURSOR;

OPEN v_cursor FOR
SELECT *
FROM employees
WHERE department_id = 10;

Easy distinction:

Parameterized Cursor
    ↓
Fixed query + variable values

REF CURSOR
↓
Cursor variable + runtime result set

24. REF CURSOR vs SYS_REFCURSOR

REF CURSOR is the concept/type family. You can define your own type:

TYPE my_ref_cursor IS REF CURSOR;

SYS_REFCURSOR is Oracle's predefined weak REF CURSOR type.

v_cursor SYS_REFCURSOR;

25. Can a REF CURSOR be strongly typed?

Yes.

DECLARE
    TYPE emp_cursor_type IS REF CURSOR
        RETURN employees%ROWTYPE;


v_cursor emp_cursor_type;


BEGIN
OPEN v_cursor FOR
SELECT *
FROM employees;


CLOSE v_cursor;


END;
/

26. Can a weak REF CURSOR point to different queries?

Yes.

DECLARE
    v_cursor SYS_REFCURSOR;
BEGIN
    OPEN v_cursor FOR
        SELECT employee_id, first_name
        FROM employees;


CLOSE v_cursor;

OPEN v_cursor FOR
    SELECT department_id, department_name
    FROM departments;

CLOSE v_cursor;


END;
/

27. Can we use a REF CURSOR in SQL directly?

Generally, no. A REF CURSOR is a PL/SQL cursor variable and result-set handle, not a table that can be queried with SELECT * FROM v_cursor.

28. Can we use a REF CURSOR in a Cursor FOR LOOP?

Yes, after opening the cursor.

DECLARE
    v_cursor SYS_REFCURSOR;
BEGIN
    OPEN v_cursor FOR
        SELECT employee_id, first_name, salary
        FROM employees;


FOR emp IN v_cursor
LOOP
    DBMS_OUTPUT.PUT_LINE(
        emp.employee_id || ' - ' ||
        emp.first_name || ' - ' ||
        emp.salary
    );
END LOOP;


END;
/

29. REF CURSOR attributes

A REF CURSOR supports cursor attributes such as %ISOPEN, %FOUND, %NOTFOUND, and %ROWCOUNT.

30. Frequently Asked Questions

Question Answer
What is a REF CURSOR? A cursor variable that points to a query result set.
What is SYS_REFCURSOR? Oracle's predefined weak REF CURSOR type.
How do we open a REF CURSOR? OPEN v_cursor FOR SELECT ...;
Can a REF CURSOR be returned from a procedure? Yes, usually through OUT SYS_REFCURSOR.
Can a function return a REF CURSOR? Yes.
Can a REF CURSOR be passed between procedures? Yes.
Can it be used with dynamic SQL? Yes.
Can a REF CURSOR be strong or weak? Yes.
Is SYS_REFCURSOR strong or weak? Weak.
Can we fetch from a REF CURSOR? Yes.

Important Exam Points

  1. REF CURSOR = cursor variable that points to a result set.
  2. SYS_REFCURSOR is a predefined weak REF CURSOR.
  3. Open it using OPEN v_cursor FOR SELECT ...;
  4. Fetch using FETCH v_cursor INTO ...;
  5. Close using CLOSE v_cursor;
  6. A REF CURSOR can be passed as a parameter.
  7. A procedure can return a result set through OUT SYS_REFCURSOR.
  8. A function can return SYS_REFCURSOR.
  9. Strong REF CURSORs specify a return row type.
  10. Weak REF CURSORs don't specify a fixed return structure.
  11. SYS_REFCURSOR is weak.
  12. REF CURSORs are useful for application-to-database result-set communication.
  13. They can be used with dynamic SQL.
  14. Cursor attributes such as %ISOPEN, %FOUND, %NOTFOUND, and %ROWCOUNT can be used appropriately.
  15. REF CURSORs are different from normal explicit cursors because the query is associated with the cursor variable when it is opened.

Easy Memory Trick

NORMAL CURSOR
    ↓
Fixed query
    ↓
Fixed cursor definition

REF CURSOR
↓
Cursor variable
↓
OPEN FOR
↓
Result set
↓
FETCH
↓
Application / PL/SQL

REF CURSOR = a cursor variable used to point to and pass a result set.

Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)