Mega Code Archive

 

Categories / Oracle PLSQL Tutorial / SQL PLUS Session Environment

 

Use variable command to define variable to reference cursor

SQL> SQL> set echo on SQL> SQL> create table t as 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; 15 rows deleted. SQL> SQL> commit; Commit complete. SQL> SQL> print x USERNAME                          USER_ID CREATED ------------------------------ ---------- --------- PLSQL                                  73 24-JUL-08 RNTSOFT                                 36 28-JUN-08 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 USERNAME                          USER_ID CREATED ------------------------------ ---------- --------- DIP                                    18 07-FEB-06 OUTLN                                  11 07-FEB-06 SYSTEM                                  5 07-FEB-06 SYS                                     0 07-FEB-06 15 rows selected. SQL> SQL> drop table t; Table dropped.