Output value in a refcursor : REFCURSOR « Cursor « Oracle PL/SQL Tutorial






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.








25.13.REFCURSOR
25.13.1.Define refcursor variable
25.13.2.Output value in a refcursor
25.13.3.The use of REF CURSOR
25.13.4.An example of using SYS_REFCURSOR for cursor variable processing
25.13.5.Cursor expressions as arguments to functions called from SQL
25.13.6.Subprograms returning resultsets by using SYS_REFCURSOR
25.13.7.Row type Reference cursor
25.13.8.Open SYS_REFCURSOR for select ... from
25.13.9.SYS_REFCURSOR type parameter