REF CURSOR

From Oracle FAQ

A REF Cursor is a datatype that holds a cursor value in the same way that a VARCHAR2 variable will hold a string value.

A REF Cursor can be opened on the server and passed to the client as a unit rather than fetching one row at a time. One can use a Ref Cursor as target of an assignment, and it can be passed as parameter to other program units. Ref Cursors are opened with an OPEN FOR statement. In most other ways they behave similar to normal cursors.

[edit] History

This feature was introduced with PL/SQL v2.3 (Oracle 7.3).

[edit] Example

Create a function that opens a cursor and returns a reference to it:

CREATE OR REPLACE FUNCTION f
  RETURN SYS_REFCURSOR
AS
  c SYS_REFCURSOR;
BEGIN
  OPEN c FOR select * from dual;
  RETURN c;
END;
/

Call above function and fetch rows from the cursor it opened:

set serveroutput on
DECLARE
 c SYS_REFCURSOR;
 v VARCHAR2(1);
BEGIN
 c := f();   -- Get ref cursor from function
 FETCH c into v;
 dbms_output.put_line('Value from cursor: '||v);
END;
/

Glossary of Terms
A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	#