Skip to content
Tutorials Center
My learning

Topics

Lesson 21 of 38 ยท Cursors, Procedures and Functions

REF Cursors and Bulk Processing in PL/SQL

Pass query results between PL/SQL and applications with REF CURSOR and SYS_REFCURSOR, then speed up loops with BULK COLLECT, LIMIT and FORALL.

A normal explicit cursor is tied to one query that is written into the block at compile time. Two problems come up quickly in real programs. First, you often want to return a result set to a caller, such as a Java, Python or PHP application. Second, row-by-row loops are slow when they run thousands of statements. REF cursors solve the first problem; BULK COLLECT and FORALL solve the second.

Oracle cannot run on this site. PL/SQL fences are read-only; the runnable blocks are labelled stand-ins written in Python and SQLite that model the idea.

REF cursors: a pointer to a result set

A REF CURSOR is a cursor variable. Instead of naming a fixed query, it holds a reference to whichever query was opened into it, so a function can open a query and hand the open cursor to its caller. The simplest way to declare one is the built-in weak type SYS_REFCURSOR:

SQL
CREATE OR REPLACE FUNCTION staff_in_department (p_dept IN NUMBER)
  RETURN SYS_REFCURSOR
IS
  v_rc SYS_REFCURSOR;
BEGIN
  OPEN v_rc FOR
    SELECT employee_id, first_name, salary
      FROM employees
     WHERE department_id = p_dept;
  RETURN v_rc;
END;
/

The caller fetches from it as from any cursor. In SQL*Plus or SQL Developer you can print it, and application drivers (JDBC, python-oracledb, ODP.NET) read it as a result set:

SQL
VARIABLE rc REFCURSOR
EXEC :rc := staff_in_department(50)
PRINT rc

There are two flavors. A weak REF CURSOR (SYS_REFCURSOR, or TYPE t IS REF CURSOR) can be opened for any query, and the compiler cannot check the row shape. A strong one names a return type, such as TYPE emp_rc IS REF CURSOR RETURN employees%ROWTYPE, so only compatible queries are accepted and errors appear at compile time rather than at run time. Use strong types inside PL/SQL code, and SYS_REFCURSOR at the boundary to client programs.

REF cursors also allow dynamic queries: OPEN v_rc FOR v_sql USING v_value; where v_sql is a string built at run time. Always pass values with USING (bind variables) and never concatenate them into the text, or you invite SQL injection and fill the shared pool with one-off statements.

The cursor belongs to the session that opened it. The caller must close it (CLOSE v_rc), or Oracle will keep the memory until the session ends.

The cost of context switches

PL/SQL and SQL run in two separate engines inside the database. Each time your PL/SQL code runs a SQL statement, control switches from the PL/SQL engine to the SQL engine and back. One switch is cheap; 100,000 of them are not. Compare:

SQL
-- Slow: 10,000 context switches
FOR r IN (SELECT employee_id FROM employees) LOOP
  UPDATE employees SET salary = salary * 1.02 WHERE employee_id = r.employee_id;
END LOOP;

-- Fast: one statement, one switch
UPDATE employees SET salary = salary * 1.02;

When pure SQL cannot do the job (you need per-row procedural logic), reduce the switches with bulk operations.

Python Counting context switches (Python stand-in)

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

BULK COLLECT: fetch many rows at once

BULK COLLECT INTO reads a whole result set into a collection with one switch:

SQL
DECLARE
  TYPE t_names IS TABLE OF employees.first_name%TYPE;
  v_names t_names;
BEGIN
  SELECT first_name BULK COLLECT INTO v_names
    FROM employees
   WHERE department_id = 50;
  DBMS_OUTPUT.PUT_LINE(v_names.COUNT || ' names loaded');
END;
/

The collection grows automatically, which is a danger: a query returning ten million rows will exhaust the session's memory (PGA). For large sets, fetch in chunks with LIMIT:

SQL
DECLARE
  CURSOR c IS SELECT employee_id, salary FROM employees;
  TYPE t_rows IS TABLE OF c%ROWTYPE;
  v_rows t_rows;
BEGIN
  OPEN c;
  LOOP
    FETCH c BULK COLLECT INTO v_rows LIMIT 1000;
    EXIT WHEN v_rows.COUNT = 0;      -- not c%NOTFOUND, see below
    -- process v_rows(1 .. v_rows.COUNT)
  END LOOP;
  CLOSE c;
END;
/

With LIMIT, test v_rows.COUNT = 0 rather than c%NOTFOUND. The last batch is usually smaller than the limit, and %NOTFOUND becomes true on that fetch, so exiting on it would skip the final rows. A typical LIMIT is 100 to 1000.

The same batching idea is easy to see in SQLite with LIMIT and OFFSET:

SQL Processing in batches (stand-in for LIMIT)

Press Run (or Ctrl + Enter) to see the result here.

FORALL: send many DML statements at once

FORALL is the write-side twin. It is not a loop but a single DML statement bound to every index of a collection:

SQL
DECLARE
  TYPE t_ids IS TABLE OF employees.employee_id%TYPE;
  v_ids t_ids := t_ids(101, 102, 103);
BEGIN
  FORALL i IN 1 .. v_ids.COUNT
    UPDATE employees SET salary = salary * 1.1
     WHERE employee_id = v_ids(i);
  DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' rows updated');
END;
/

After a FORALL, SQL%ROWCOUNT is the total over all iterations, and SQL%BULK_ROWCOUNT(i) gives the count for iteration i. If one iteration fails, the whole statement normally stops and rolls back its own changes. Add SAVE EXCEPTIONS to keep going and collect every error in SQL%BULK_EXCEPTIONS (handle ORA-24381), covered in user-defined exceptions. Use INDICES OF or VALUES OF when the collection is sparse.

Choosing the right tool

  • One SQL statement can do it: write that statement.
  • Per-row logic, read side: cursor FOR loop (already batched by 100) or BULK COLLECT ... LIMIT.
  • Per-row logic, write side: FORALL.
  • Return rows to a client: SYS_REFCURSOR.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What is the main reason bulk operations are faster than row-by-row loops?

    Each SQL call from PL/SQL costs a switch. Bulk operations move many rows per switch.
  2. 2. With FETCH ... BULK COLLECT ... LIMIT, which exit condition is safe?

    The final batch can be partial, and %NOTFOUND is already true for it, so exiting on it would skip those rows.
  3. 3. Which declaration lets a function return an open query to a client application?

    A REF CURSOR variable points to the open query, and the caller fetches from it.
  4. 4. What does FORALL do?

    FORALL is a single DML statement with an index range, not an ordinary loop.

Summary

  • A REF CURSOR is a cursor variable. SYS_REFCURSOR is the standard weak type for returning result sets to callers.
  • Pass values to dynamic queries with USING, never by concatenation.
  • PL/SQL to SQL context switches add up. Prefer pure SQL; otherwise batch with BULK COLLECT ... LIMIT and FORALL.
  • Use v_rows.COUNT = 0 as the exit test in a LIMIT loop.

Next: Stored Procedures