Define refcursor variable : REFCURSOR « Cursor « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. Cursor
3. REFCURSOR

SQL> set echo on
SQL>
SQL> set timing off
SQL>
SQL> create table t
  2  as
  3  select * from all_users;

Table created.

SQL>
SQL> variable x refcursor
SQL>
SQL> begin
  2          open :x for select * from t;
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL>
SQL> delete from t;

17 rows deleted.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> print x


USERNAME                          USER_ID CREATED
------------------------------ ---------- ---------
USER1                                  55 02-JUN-07
JAVA2S                                 38 09-SEP-06
BOB                                    36 30-AUG-06
FLOWS_020100                           35 07-FEB-06
FLOWS_FILES                            34 07-FEB-06
HR                                     33 07-FEB-06
MDSYS                                  32 07-FEB-06
ANONYMOUS                              28 07-FEB-06
XDB                                    27 07-FEB-06
CTXSYS                                 25 07-FEB-06
DBSNMP                                 23 07-FEB-06
ANEWUSER                               44 09-SEP-06
TSMSYS                                 20 07-FEB-06
DIP                                    18 07-FEB-06
OUTLN                                  11 07-FEB-06
SYSTEM                                  5 07-FEB-06
SYS                                     0 07-FEB-06

17 rows selected.

SQL>
SQL> drop table t;

Table dropped.