Script categories

Wednesday, 23 November 2011

Implicit Savepoint when deadlock detection occurs.

The following example describes how Oracle
sets an implicit Savepoint after each UPDATE.

For this example, when a deadlock error occurs, ORA-00060, not all
DML statements in the transaction will therefore be rolled back,
but only the single statement within the session in which the deadlock was
detected.

The following illustrates the point;

Create a test table and data first;

create table a (x number);

insert all
  into a (x) values (1)
  into a (x) values (2)
select 1
from dual;

commit;

The data would look as follows;

SQL> select * from a;

         X
----------
         1
         2
        
Now issue a series of statements in two sessions to generate a deadlock error;

Time DML Statement                       Notes
==== =============                       =====          
     Session 1         Session 2
     =========         =========

t0   update a
     set    x = 3
     where  x = 1;
--------------------------------------------------------------------
t1                     update a
                       set    x = 4
                       where  x = 2;
--------------------------------------------------------------------
t2   update a                        At this point Session 1
     set    x = 6                    hangs, because Session 2
     where  x = 2;                   has issued an UPDATE on
                                     the same row at point t1.
--------------------------------------------------------------------
t3                     update a      At this point Session 2
                       set    x = 5  hangs as well.                        where  x = 1; Session 1 is now waiting
                                     for a row that Session 2 
                                     has locked while Session 2 
                                     is waiting for a row that 
                                     Session 1 has locked.
--------------------------------------------------------------------
t4  update                           At this point Oracle will
    a set   x = 6                    will detect a deadly
    where   x = 2;                   embrace (deadlock) due to
                                     a perpetual wait for
    update a set                     shareable resources as
        *                            described at point T3.
    ERROR at line 1:
    ORA-00060:
    deadlock detected
    while waiting for
    resource
--------------------------------------------------------------------
t5  commit;                          The waiting in Session 2
                                     ceases and the UPDATE
                                     shows as being
                                     successfully completed.
--------------------------------------------------------------------
t6  SQL> select *                    At this point the data
      2  from a;                     indicates that 1 was
                                     successfully updated to 3,
             X                       but 2 was not.
             -
             3
             2                                 
            
The reason that one of the updates appeared to have worked, when viewing
the data at point T6, is that Oracle created an implicit Savepoint at point
T2, so when the error was encountered at T5, the entire transaction was not
rolled back, but only the single UPDATE in which the deadlock was detected.

Session 2 would have continued to hang (wait) at point T4, even though a
partial rollback occurred to an implicit savepoint due to the deadlock detection, since the row, updated at T3, was locked at T0.

The UPDATE in session 2 was successful once a COMMIT was issued, at T5,
in session 1 however.

Use this SQL to view the number of deadlocks that have occurred within the database;

SQL> select name, value
  2  from v$sysstat
  3  where name = 'enqueue deadlocks';

NAME                                         VALUE
--------------------------------------- ----------
enqueue deadlocks                                3

To avoid deadlocks, always lock rows in the same order,

For example, for the above set of transactions, had the order
of UPDATES occurred as follows;


t0   update a
     set    x = 3
     where  x = 1;
------------------------------------
t1                     update a
                       set    x = 5
                       where  x = 1;
------------------------------------
t2   update a                      
     set    x = 6                  
     where  x = 2;                 
                                   
------------------------------------
t3                     update a    
                       set    x = 4
                       where  x = 2;

Then a deadlock error would not have occurred.

Identifying lock modes

The SQL in the post "Identify blocking session for an UPDATE",
bluefrog-oracle.blogspot.com/2011/11/identify-blocking-session-for-update.html
highlighted how to identify which sessions were blocking and which were waiting.

The sample output for the SQL in the above post would be something like;

BLOCKING_STATUS
-------------------------------------------------------------------
USER1@XX\HOST ( SID=214 )  is blocking USER1@XX\HOST ( SID=26 )


The scenario, illustrated with the above output, is that a session, connected as USER1, is blocking a session, also connected as USER1.

The SQL below shows more detail in relation to the types of
locks and modes that are causing blocking for each session.

The script is meant for running on a RAC server.

col User_Session  format a10
col object_name   format a40
col sid           format 99999999
col type          format a4
col serial#       format 999999999
col held          format 9999
col request       format 99999999
col Inst_ID       format 99999
col machine       format a30

set linesize 400
set pagesize 30000

select l.*, s.machine, s.osuser
from
    (
    select  /*+ all_rows */
            l.Inst_ID
           ,s.sid           SID
           ,s.username      User_Session
           ,s.serial#       Serial#
           ,l.type          type
           ,' '             object_name
           ,lmode           held
           ,request         request
    from
            gv$lock          l
           ,gv$session       s
           ,gv$process       p
    where
            s.sid           = l.sid
    and     s.username      <> ' '
    and     s.paddr         = p.addr
    and     l.type          <> 'TM'
    and    (l.type <> 'TX' or l.type = 'TX' and l.lmode <> 6)
    union
    select  /*+ all_rows */
            l.Inst_ID
           ,s.sid           SID
           ,s.Username      User_Session
           ,s.serial#       Serial#
           ,l.type          type
           ,object_name     object_name
           ,lmode           held
           ,request         request
    from
            gv$lock          l
           ,gv$session       s
           ,gv$process       p
           ,sys.dba_objects o
    where
            s.sid           = l.sid
    and     o.object_id     = l.id1
    and     l.type          = 'TM'
    and     s.username      <> ' '
    and     s.paddr         = p.addr
    union
    select  /*+ all_rows */
            l.Inst_ID
           ,s.sid SID
           ,s.Username      User_Session
           ,s.serial#       Serial#
           ,l.type          type
           ,'(Rollback='||rtrim(r.name)||')' object_name
           ,lmode           held
           ,request         request
    from
            gv$lock          l
           ,gv$session       s
           ,gv$process       p
           ,v$rollname       r
    where
            s.sid           = l.sid
    and     l.type          = 'TX'
    and     l.lmode         = 6
    and     trunc(l.id1/65536) = r.usn
    and     s.username      <> ' '
    and     s.paddr         = p.addr
   )          l
  ,v$session  s
where
          s.sid         = l.sid
order by l.Object_Name, l.Held
/

There are only two types of general locks, shared and exclusive.

A many to one relationship can exist between several shared locks and
a single resource, whereas only a one to one scenario can exist
between an exclusive lock and a single resource.

Oracle manages locks on resources through enqueues.
Enqueues as shared memory structures that serialize access to database
resources.

A database resource, in relation to data locks, can be an object structure
or a transaction.

Oracle has two types of data locks:

TX = Row Locks - Finest grain of locking possible.
Oracle stores locking information of a locked row
within the data block the row resides in.

A row lock automatically generates a table lock (TM), to prevent
DDL from occurring on the table while the transaction is in progress.

TM = Table locks - Acquired by a transaction (INSERT,UPDATE,DELETE,MERGE,LOCK TABLE or SELECT FOR UPDATE).

Table locks can be one of six modes, namely;

0 = none
1 = null
 
2 = row-S (RS or SS)     
Row share lock or sub-share lock. 
Transaction holds locked rows and an update is pending.
Acquired when issuing either;
  SELECT FOR UPDATE;
  LOCK TABLE IN ROW SHARE mode;

Least restrictive, since other transactions can query, insert update or delete concurrently on the same table on rows other than the ones locked with SELECT for UPDATE.
Other transactions can therefore obtain RS, RX or SRX locks.
Prevents an exlusive table (X) lock however.

3 = row-X (RX or SX)
Row exclusive lock. Transaction holding locked rows has completed the update(s) to the table.
Acquired automatically with one of the following four statements;
  INSERT;
  UPDATE;
  DELETE;
  LOCK TABLE IN ROW EXCLUSIVE MODE;

More restrictive than row share lock, since other transactions cannot concurrently
lock the table in share mode (S), share exclusive mode (SRX) using one of the following statements;
  LOCK TABLE IN SHARE MODE
  LOCK TABLE IN EXCLUSIVE MODE
in addition to not permitting any other types of exclusive (X) locks.
RX is the default locking behavior for Oracle.

4 = share (S)
Share Table. Acquired when explicitly issuing the LOCK table in SHARE MODE statement.
Only allows other transactions to query the table in share mode (S).
Prevents other transactions from modifying the same table in SRX, RX and X modes.
Transactions can hold share (S) table locks concurrently, but a SELECT for UPDATE update can only occur if there are no other transactions in share mode (S) as well.

5 = S/Row-X (SRX or SSX) 
Share Row Exclusive. Acquired explicitly when issuing a LOCK TABLE in SHARE ROW EXCLUSIVE mode. Only a single transaction at a time can acquire a share row exclusive lock.
Other transactions can however query the table, but they cannot update the table.
Prevents all types of locks from other transactions. 
Permits SELECT FOR UPDATE locks within the same transaction after issuing the LOCK TABLE statement.

6 = Exclusive (X)        
Most restrictive. Prevents any DML on the table. Acquired with
LOCK TABLE IN EXCLUSIVE MODE. Only a single transaction can obtain an exclusive lock at a time. Querying can still occur though.


This is a sample output from the above query and one can clearly

INST_ID  SID USER_SESSI SERIAL# TYPE OBJECT_NAME HELD REQUEST
------- ---- ---------- ------- ---- ----------- ---- -------
      1  222 USER2        34254 TX                  0       6
      1  221 USER1         8366 TM   TEST_LOCK      3       0


The REQUEST column will indicate the mode of lock on a Resource
that is sought, which is currently held by another
session (indicated by HELD).

A Resource can be a table definition, a transaction or any structure
that is shareable. The allowable range of values will be the same as
per the HELD column.

Oracle uses enqueues as a locking mechanisms for queuing access to
resources.

Lock conversion occurs when a lock with a lower restrictiveness is converted
to one of higher restrictiveness. For example, if a SELECT for UPDATE
is issued, the initial locks on the table would be row share (RS).

When eventually the UPDATE WHERE CURRENT OF occurs, the RS lock will be converted
to a RX lock.

Thursday, 17 November 2011

Describing a ref cursor using the DBMS_SQL API

The following example illustrates how to describe the columns and values of a reference cursor, created from a dynamic DML statement using the DBMS_SQL API.

Firstly, create a test table with some sample data;


create table Trade_Deal
(Deal_ID      number
,Deal_Type    varchar2(10)
,Deal_Version integer
,Created      timestamp
,Trade_Risk   integer
,GBP_Amnt     number
,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;

commit;

Next, create a procedure to test the conversion of a result set
to a reference cursor;

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 create a procedure, which describes the cursor which
was converted in the previous procedure.

create or replace
procedure Show_Ref_Cursor (p_Ref in sys_refcursor) as

   v_Ref              sys_refcursor := p_Ref;
   -- Do not use the "dbms_sql.desc_tab" type as it is deprecated
   v_Ref_Desc         dbms_sql.desc_tab2;
   v_Ref_Cols         binary_integer;
   v_Cursor           binary_integer;

begin

  -- Convert cursor, passed as a parameter, to a DBMS_SQL cursor
  v_Cursor := dbms_sql.to_cursor_number(v_Ref);

  -- Get a description of the cursor
  dbms_sql.describe_columns2(v_Cursor, v_Ref_Cols, v_Ref_Desc);
  dbms_sql.close_cursor(v_Cursor);

  /*
  Show a description of columns within cursor.
  The column type integer, for all columns that are
  of type SQL NCHAR types (NCHAR, NVARCHAR2, NCLOB),
  is the same as for (CHAR, VARCHAR2, CLOB).

  In such cases refer to the ".COL_CHARSETID" attribute.
  */

  for i in 1 .. v_Ref_Cols loop
    dbms_output.put('Column ' || i || ': '
    || rpad(v_Ref_Desc(i).col_name,10)
    || '; Type: ' ||  case v_Ref_Desc(i).col_type
                        when 1 then
                          'VARCHAR2'
                        when 2 then
                          'NUMBER'
                        when 8 then
                          'LONG'
                        when 11 then
                          'ROWID'
                        when 12 then
                          'DATE'
                        when 23 then
                          'RAW'
                        when 96 then
                          'CHAR'
                        when 100 then
                          'BINARY_FLOAT'
                        when 101 then
                          'BINARY_DOUBLE'
                        when 112 then
                          'CLOB'
                        when 113 then
                          'BLOB'
                        when 114 then
                          'BFILE'
                        when 180 then
                          'TIMESTAMP'
                        when 181 then
                          'TIMESTAMP WITH TIME ZONE'
                        when 182 then
                          'INTERVAL YEAR TO MONTH'
                        when 231 then
                          'TIMESTAMP WITH LOCAL TIME ZONE'
                        else
                          'UNDEFINED'
                      end);
      dbms_output.new_line;
   end loop;

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.

A useful query you may want to often use when dealing with
dynamic SQL (especially long running SQL) is to view
the bind variables and their respective values.

This is an example query to extract the bind variable
submitted in procedure GET_DEALS, which was executed
in the previous step.

col SQL_Text        format a50
col Bin_Var_Name    format a20
col Value_String    format a20
col Datatype_String format a20

SQL> select substr(SQL_Text,1,50) SQL_Text
  2        ,Bind_Var_Name
  3        ,Value_String
  4        ,Datatype_String
  5  from
  6    (
  7      select sq.sql_text          as SQL_Text
  8            ,sbc.name             as Bind_Var_name
  9            ,sbc.Value_String     as Value_String
 10            ,sbc.datatype_string  as Datatype_String
 11      from
 12            v$sql_bind_capture sbc
 13      inner join v$sql sq on sq.sql_id = sbc.sql_id
 14      -- modify predicate to obtain the SQL you looking for
 15      where sq.sql_text like 'select%'
 16      and   sq.sql_text like '%Trade_Deal%'
 17      order by sbc.last_captured desc
 18    )
 19  where rownum = 1
 20  /

SQL_TEXT      BIND_VAR_NAME VALUE_STRING DATATYPE_STRING
------------- ------------- ------------ ---------------
select  td.nc :DT           SPOT         VARCHAR2(32)

Verify the Reference cursor is populated;

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

Rerun the procedure to open the reference cursor, since
the "print :Test_Ref" closes the cursor.

variable test_Ref refcursor;
set serveroutput on
begin
  Get_Deals('SPOT', :Test_Ref);
end;
/

Finally, Describe the cursor using the DBMS_SQL API

SQL> exec Show_Ref_Cursor(:Test_Ref);
Column 1: DEAL_ID   ; Type: NUMBER
Column 2: DEAL_TYPE ; Type: VARCHAR2
Column 3: CREATED   ; Type: TIMESTAMP
Column 4: TRADE_RISK; Type: NUMBER
Column 5: GBP_AMNT  ; Type: NUMBER

PL/SQL procedure successfully completed.

Note, a bug exists in 11g Release 1 and 2 whereby the DBMS_SQL.DESCRIBE does not output the correct precision for a NUMBER column that was added onto a table using the ALTER statement with a DEFAULT value.

The bug reference on Metalink is;
Bug 9040420: PLEASE RE-REVIEW BUG 7489902

The bug has been fixed in 11.2.0.2

As a workaround for earlier versions of 11g, use the following;
alter system set "_add_col_optim_enabled"=FALSE;

--------------------------------------------------------------
For columns that are User defined (NESTED TABLES, TYPES), use
the DBMS_SQL.DESC_TAB3 along with DBMS_SQL.DESCRIBE_COLUMNS3,
data type and procedure, instead of DBMS_SQL.DESC_TAB2
and DBMS_SQL.DESCRIBE_COLUMNS2.

DBMS_SQL.DESC_TAB3 along with DBMS_SQL.DESCRIBE_COLUMNS3 hold
two additional attributes COL_TYPE_NAME and COL_TYPE_NAME_LEN
to describe user defined types.

The full list of attributes in DESC_TAB3 is;
type desc_rec3 is record
(
  col_type            binary_integer  := 0,
  col_max_len         binary_integer  := 0,
  col_name            varchar2(32767) := '',
  col_name_len        binary_integer  := 0,
  col_schema_name     varchar2(32)    := '',
  col_schema_name_len binary_integer  := 0,
  col_precision       binary_integer  := 0,
  col_scale           binary_integer  := 0,
  col_charsetid       binary_integer  := 0,
  col_charsetform     binary_integer  := 0,
  col_null_ok         boolean         := true,
  col_type_name       varchar2(32767) := '',
  col_type_name_len   binary_integer  := 0
);

The example that follows describes a user defined type;

Create a sample user defined type first;

create or replace type ty_Deal_Type as object
(Deal_Type varchar2(10)
,Business_Unit  varchar2(10)
,Location varchar2(10)
);
/

create or replace type tb_Deal_Type as table of ty_Deal_Type;
/

Add a column, tb_DT, as a user defined type "TB_DEAL_TYPE"
in the Trade_Deal table;


SQL> alter table trade_deal add tb_DT tb_Deal_Type
  2  nested table tb_DT store as tb_Deal_Type_Col;
Table altered.

SQL> desc trade_deal;
 Name                          Null?    Type
 ----------------------------- -------- ------------
 DEAL_ID                                NUMBER
 DEAL_TYPE                              VARCHAR2(10)
 DEAL_VERSION                           NUMBER(38)
 CREATED                                TIMESTAMP(6)
 TRADE_RISK                             NUMBER(38)
 GBP_AMNT                               NUMBER
 USD_AMNT                               NUMBER
 TB_DT                                  TB_DEAL_TYPE

Modify the GET_DEALS procedure to include the tb_DT in the output;

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

   v_SQL := 'select  td.Deal_ID
                    ,td.Deal_Type
                    ,td.Created
                    ,td.Trade_Risk
                    ,td.GBP_Amnt
                    ,td.tb_DT
              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 Get_Deals;
/

Next, modify the SHOW_REF_CURSOR procedure to accommodate
for user defined types;


create or replace
procedure Show_Ref_Cursor (p_Ref in sys_refcursor) as

   v_Ref              sys_refcursor := p_Ref;
   -- Do not use the "dbms_sql.desc_tab" type as it is deprecated
   v_Ref_Desc         dbms_sql.desc_tab3;
   v_Ref_Cols         binary_integer;
   v_Cursor           binary_integer;

begin

  -- Convert cursor, passed as a parameter, to a DBMS_SQL cursor
  v_Cursor := dbms_sql.to_cursor_number(v_Ref);

  -- Get a description of the cursor
  dbms_sql.describe_columns3(v_Cursor, v_Ref_Cols, v_Ref_Desc);
  dbms_sql.close_cursor(v_Cursor);

  for i in 1 .. v_Ref_Cols loop
    dbms_output.put('Column ' || i || ': '
    ||rpad(v_Ref_Desc(i).col_name,10)
    ||'; Type: '||case v_Ref_Desc(i).col_type
                    when 1 then
                      'VARCHAR2'
                    when 2 then
                      'NUMBER'
                    when 8 then
                      'LONG'
                    when 11 then
                      'ROWID'
                    when 12 then
                      'DATE'
                    when 23 then
                      'RAW'
                    when 96 then
                      'CHAR'
                    when 100 then
                      'BINARY_FLOAT'
                    when 101 then
                      'BINARY_DOUBLE'
                    when 112 then
                      'CLOB'
                    when 113 then
                      'BLOB'
                    when 114 then
                      'BFILE'
                    when 180 then
                      'TIMESTAMP'
                    when 181 then
                      'TIMESTAMP WITH TIME ZONE'
                    when 182 then
                      'INTERVAL YEAR TO MONTH'
                    when 231 then
                      'TIMESTAMP WITH LOCAL TIME ZONE'
                    when 109 then
                    -- the "col_type_name" attribute is only
                    -- available when using the DESC_TAB3 type
                      'USER DEFINED: '||v_Ref_Desc(i).col_type_name
                  end);
      dbms_output.new_line;
   end loop;

end Show_Ref_Cursor;
/

Run the test again to view output for the user defined type;
variable Test_Ref refcursor;



set serveroutput on
variable Test_ref refcursor;
begin
  Get_Deals('SPOT', :Test_Ref);
end;
/

SQL> exec Show_Ref_Cursor(:Test_Ref);
Column 1: DEAL_ID   ; Type: NUMBER
Column 2: DEAL_TYPE ; Type: VARCHAR2
Column 3: CREATED   ; Type: TIMESTAMP
Column 4: TRADE_RISK; Type: NUMBER
Column 5: GBP_AMNT  ; Type: NUMBER
Column 6: TB_DT     ; Type: USER DEFINED: TB_DEAL_TYPE

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