What is a REF CURSOR?

REF CURSORs actually managed to slip into play in the days of ORACLE 7.3 but have really taken off in later versions. They are ORACLE's way of returning a result set from the database server in a similar way to Sybase and SQL Server stored procedures.

It used to be the case that one would have to define a user-defined type to represent the cursor handle but ORACLE 9i has conveniently defined this for us with the SYS_REFCURSOR type.

An example stored procedure that returns a result set (figure 1.0):

CREATE OR REPLACE PROCEDURE get_enames(p_result OUT SYS_REFCURSOR)
AS
BEGIN
  OPEN p_result FOR
    SELECT ename FROM emp ORDER BY ename;
END get_enames;

An example of how this can be used in SQL*Plus (figure 1.1):

sql> variable rs refcursor
sql> execute get_enames(:rs);

PL/SQL procedure successfully completed.

sql> print rs

ENAME
----------
ADAMS
ALLEN
BLAKE
CLARK
FORD
JAMES
JONES
KING
MARTIN
MILLER
SCOTT
SMITH
TURNER
WARD

14 rows selected.

sql>