Mega Code Archive

 

Categories / Oracle PLSQL Tutorial / Cursor

 

Define refcursor variable

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 RNTSOFT                                 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.