Cursor Variables in PL/SQL – REF CURSOR & Subquery Examples

Cursor Variables in PL/SQL (also called REF CURSORs) allow developers to build flexible, reusable, and dynamic queries that return result sets at runtime. Unlike static explicit cursors, cursor variables can point to different queries during execution.

What Are Cursor Variables in PL/SQL?

A cursor variable is a pointer to a context area created by Oracle. Unlike explicit cursors, which are tied to a specific query, cursor variables in PL/SQL allow the same variable to be reused for different queries.

They are ideal for:

Returning result sets from stored procedures
Dynamic query execution
Passing result sets between programs or modules

Declaring Cursor Variables in PL/SQL

Cursor variables must be declared using REF CURSOR, either with a user-defined type or using the built-in SYS_REFCURSOR.

TYPE book_typ IS REF CURSOR RETURN books%ROWTYPE;
cv_books book_typ;

Or simply:

cv_books SYS_REFCURSOR;

SET SERVEROUTPUT ON
DECLARE
  TYPE book_typ IS REF CURSOR RETURN BOOKS%ROWTYPE;
  cv_books book_typ;
  v_books BOOKS%ROWTYPE;
BEGIN
  OPEN cv_books FOR
    SELECT * FROM books WHERE isbn = '78824389';
  FETCH cv_books INTO v_books;
  DBMS_OUTPUT.PUT_LINE(v_books.title || ' is ' || v_books.price);
  CLOSE cv_books;
END;
/

Example 1 – Basic Cursor Variable with REF CURSOR

SET SERVEROUTPUT ON
DECLARE
  TYPE book_typ IS REF CURSOR RETURN BOOKS%ROWTYPE;
  cv_books book_typ;
  v_books BOOKS%ROWTYPE;
BEGIN
  OPEN cv_books FOR
    SELECT * FROM books WHERE isbn = '78824389';
  FETCH cv_books INTO v_books;
  DBMS_OUTPUT.PUT_LINE(v_books.title || ' is ' || v_books.price);
  CLOSE cv_books;
END;
/

Example 2 – Returning Result Set from a Procedure

CREATE OR REPLACE PROCEDURE authors_sel (cv_results IN OUT SYS_REFCURSOR)
IS
BEGIN
  OPEN cv_results FOR
    SELECT id, first_name, last_name FROM authors;
END;
/

Run it in SQL*Plus:

VARIABLE x REFCURSOR
EXEC authors_sel(:x)
PRINT x

Cursor Subqueries (Nested Cursors)

Cursor subqueries allow embedding one cursor inside another. They can be used with REF CURSOR and explicit cursors (not implicit cursors).

Example 3 – Cursor Subquery in Explicit Cursor

DECLARE
  cv_author SYS_REFCURSOR;
  v_title BOOKS.TITLE%TYPE;
  v_author AUTHORS%ROWTYPE;
  CURSOR book_cur IS
    SELECT b.title,
      CURSOR (
        SELECT * FROM authors a
        WHERE a.id IN (b.author1, b.author2, b.author3)
      )
    FROM books b WHERE isbn = '78824389';
BEGIN
  OPEN book_cur;
  LOOP
    FETCH book_cur INTO v_title, cv_author;
    EXIT WHEN book_cur%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('Title: ' || v_title);
    LOOP
      FETCH cv_author INTO v_author;
      EXIT WHEN cv_author%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE('Author: ' || v_author.first_name || ' ' || v_author.last_name);
    END LOOP;
  END LOOP;
  CLOSE book_cur;
END;
/

Open Cursor Limits

Oracle limits how many cursors you can keep open at once. To view the limit:

SELECT value FROM v$parameter WHERE name = 'open_cursors';

Set it higher in init.ora or you might face:

ORA-01000: maximum open cursors exceeded

Best Practices for Cursor Variables in PL/SQL

Always close cursor variables unless you’re passing them to clients
Use SYS_REFCURSOR for simplicity
Avoid memory leaks by releasing cursors not needed
Use cursor subqueries carefully—they consume more memory

Cursor Variables vs Explicit Cursors

Feature	Cursor Variables (REF CURSOR)	Explicit Cursors
Reusable Query?	✅ Yes	❌ No
Return from Proc?	✅ Yes	❌ No
Compile-Time Binding	❌ No	✅ Yes
Subqueries Support	✅ Yes	✅ Yes
Dynamic SQL Support	✅ Yes	❌ No

AmantPoint Exclusive Learning Series
© 2025 AmantPoint. All rights reserved. Educational content for developers and learners.