Converting a result to a ref cursor using DBM_SQL

The following example illustrates how to convert a result
set, created from a dynamic DML statement, to a reference cursor.

In 11g Release 2, the converted reference cursor can then be passed, as a parameter, onto a Java class or any PL/SQL procedure for further processing. In 11g Release 1, the reference cursor could only be passed as a parameter to PL/SQL procedures.

Firstly, create a test table with some sample data;

create table Trade_Deal
(Deal_ID      number          not null
,Deal_Type    varchar2(10)
,Deal_Version integer
,Created      timestamp default current_timestamp
,Trade_Risk   integer check (Trade_Risk in (1,2,3,4,5,6,7,8,9))
,GBP_Amnt     number          not null
,USD_Amnt     number);

insert all
  into Trade_Deal values(1,'SPOT',   1,current_timestamp,1,10,null)
  into Trade_Deal values(2,'FORWARD',1,current_timestamp,1,12,null)
  into Trade_Deal values(3,'DEPOSIT',1,current_timestamp,1,11,null)
  into Trade_Deal values(4,'SPOT',   1,current_timestamp,1,22,null)
select 1
from dual
/

Next, create a procedure to test with;
create or replace
procedure Get_Deals(p_Deal_Type in varchar2
                   ,p_Ref      out sys_refcursor) as

   v_SQL     clob;
   v_Cursor  binary_integer := dbms_sql.open_cursor;
   v_Ref     sys_refcursor;
   v_Exec    binary_integer;

begin

  -- Note, there is no reason to execute the following SELECT as a
  -- dynamic statement, the reason for doing so is simply
  -- to illustrate that a reference cursor can be pointed to a
  -- result set using the DBMS_SQL API.

   v_SQL := 'select td.Deal_ID
                   ,td.Deal_Type
                   ,td.Created
                   ,td.Trade_Risk
                   ,td.GBP_Amnt
              from
                    Trade_Deal td
              where
                    td.Deal_Type = :DT';

   dbms_sql.parse(v_Cursor, v_SQL, dbms_sql.native);

   dbms_sql.bind_variable(v_Cursor, 'DT', p_Deal_Type);

   v_Exec := dbms_sql.execute(v_Cursor);

   -- Convert to a REF cursor after execution
   v_Ref := dbms_sql.to_refcursor(v_Cursor);

   p_Ref := v_Ref;

end;
/

Now for the test;

SQL> variable test_Ref refcursor;
SQL> set serveroutput on
SQL> begin
  2    Get_Deals('SPOT', :Test_Ref);
  3  end;
  4  /

PL/SQL procedure successfully completed.


Verfify the results;

SQL> print :Test_Ref;

DEAL_ID DEAL_TYPE CREATED                   TRADE_RISK GBP_AMNT
------- --------- ------------------------- ---------- --------
      1 SPOT      16-NOV-11 11.04.57.957000          1       10
      4 SPOT      16-NOV-11 11.04.57.957000          1       22