Define and use reference cursor : Reference Cursor « Cursor « Oracle PL / SQL






Define and use reference cursor

 

SQL> -- Define and use reference cursor
SQL>
SQL> create table myTable as select * from all_users;

Table created.

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

PL/SQL procedure successfully completed.

SQL>
SQL> delete from myTable;

14 rows deleted.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> print myReferenceCursor;

USERNAME                          USER_ID CREATED
------------------------------ ---------- ---------
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
TSMSYS                                 20 07-FEB-06
DIP                                    18 07-FEB-06

USERNAME                          USER_ID CREATED
------------------------------ ---------- ---------
OUTLN                                  11 07-FEB-06
SYSTEM                                  5 07-FEB-06
SYS                                     0 07-FEB-06

14 rows selected.

SQL>
SQL>
SQL> drop table myTable;

Table dropped.

SQL>
SQL>

           
         
  








Related examples in the same category

1.Defines and declares a reference cursor before explicitly opening it
2.REF CURSOR custom type
3.Reference cursor demo
4.Reference value in a cursor by cursor variable
5.Set the REFCURSOR variable to the results of a SELECT statement, and print out the REFCURSOR variable
6.refcursor variable