variable refcursor

Variabel can be declared as reference cursor in sql and record sets can be stored into it. A simle exapmple is shown below.


SQL> variable test_cur refcursor
SQL> declare
2 v_sql varchar2(3000);
3 begin
4 v_sql:='select * from client_pricing order by 2,1';
5 open :test_cur for v_sql;
6 end;
7 /

PL/SQL procedure successfully completed.

SQL> print test_cur

FUNCTION_ID CLIENT_ID PRICING
----------- ---------- ----------
1 10 44
1 10 22
1 20 33
2 20 33
1 30 33
2 30 55
1 40 22
2 40 77
1 50 44
2 50 99

10 rows selected.

SQL> spool off