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

Report historical wait times per event for a specific Table

I always try and name my constraint and index names
with the same prefix as my table name and then suffix it with
a few characters to indicate what type of constraint or index it is.

For Example;

For a table such as TRADE_DEAL;

create table Trade_Deal
(Deal_ID      number          not null
,Deal_Type    varchar2(10)
,Deal_version integer
,Created      timestamp default current_timestamp
,Trade_Risk   integer
,GBP_Amnt     number          not null
,USD_Amnt     number);

The primary key would be named as follows;

alter table trade_deal 
add constraint TRADE_DEAL_PK primary key (deal_id);

A check constraint might be named as follows;

alter table trade_deal add constraint
TRADE_DEAL_CK_RISK check (trade_risk in (1,2,3,4,5,6,7,8,9));

The main reason for naming my objects using the above convention
is that it simplifies running queries to asses performance
stats on a particular set of related objects.

For example, the AWR query below will output the time waited
per wait event for a set of related table and indexes.

First set the start and end snapshot ID as variables before
attempting to run the query below. Use this script if you want;
http://bluefrog-oracle.blogspot.com/2011/11/set-start-and-end-snapshot-id-for-awr.html

variable p_Schema varchar2(30);
exec :p_Schema := sys_context('USERENV','CURRENT_SCHEMA');

col Object_Name format a32
col Object_Type format a10
col Event_Name  format a40
select
  /*+ all_rows */
   ds.Instance        as Instance_Number
  ,ao.Object_Name     as Object_Name
  ,ao.Object_Type     as Object_Type
  ,ds.Event           as Event_Name
  ,sum(ds.Cnt)        as Event_Wait_Cnt
  ,sum(Time_Waited)   as Time_Waited
from
  (
   select    /*+ all_rows */
             au.Username
            ,e.Name          as Event
            ,count(*)        as Cnt
            ,sum(Wait_Time)  as Time_Waited
            ,da.Current_Obj# as Object_ID
            ,Instance_Number as Instance
   from
             dba_hist_active_sess_history da
            ,v$Event_Name e
            ,all_users   au
   where     da.Event_ID = e.Event_ID
   and       da.User_ID  = au.User_ID
   and       da.Snap_ID  between :p_Start_Snap_ID and :p_End_Snap_ID
   and       au.Username = :p_Schema
   group by  au.Username
            ,e.Name
            ,da.Current_Obj#
            ,da.Instance_Number
   order by 3 desc
  )                     ds
  ,dba_Objects          ao
where
          ao.Object_ID          = ds.Object_ID
and       ao.Object_name        like  '&Object%'
and       ao.Object_Type        in ('TABLE','INDEX')
-- Exclude SQL Client type of waits
and       ds.Event              not like 'SQL*Net%'
group by  ao.Object_Name
         ,ao.Object_Type
         ,ds.Event
         ,ds.Instance
order by 5 desc, 4
/

Combining FORALL with BULK COLLECT

First create a few test tables along with some sample data;

create or replace type varchar2_ntt as table of varchar2(4000);
/

create or replace type number_ntt as table of number;
/

create type ty_Deal as object (Deal_ID number
                              ,Deal_Type varchar2(10)
                              ,Currency varchar2(10)
                              ,Convert_Rate   number
                              ,Converted_Amnt number);
/

create type tb_Deal as table of ty_Deal;
/

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);

create table FX_Rate
(FX_Rate_ID   number        not null
,FX_Rate_Date timestamp     not null
,Deal_Type    varchar2(10)  not null
,FX_From_Cy   varchar2(3)   not null
,FX_To_Cy     varchar2(3)   not null
,FX_Rate      number        not null
);

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
/

insert all
 into FX_Rate values (1,current_timestamp,'SPOT','GBP','USD',1.5757)
 into FX_Rate values (2,current_timestamp,'SPOT','USD','GBP',0.6336)
select 1
from dual

commit;


The goal of the procedure is to update the USD_AMNT colunm
on the TRADE_DEAL table based on the latest foreign exchange
rate available in the FX_RATE table for the given DEAL_TYPE.

The output will be a collection of updated Deals, which could
then be passed further onto a separate PL/SQL procedure or Java
class as a parameter.

The benefit of using BULK COLLECT with
a FORALL is that other PL/SQL procedures or Java classes do not have
to query the database again to identify which deals were updated since
they're all in the output collection that was populated as a result
of the FORALL UPDATE. 
The output collection could be passed on as a parameter.

Create a procedure to illustrate the  point;

create or replace procedure Update_To_Currency
  (
   p_Deal_Type  in Trade_Deal.Deal_Type%type
  ,p_To_Cy      in FX_Rate.FX_To_Cy%type
  ,p_From_Cy    in FX_Rate.FX_From_Cy%type
  ,p_Latest     in timestamp
  ,p_tb_Deal    out tb_Deal
  ) as

  tb_Spot         tb_Deal := tb_Deal(); -- input  collection
  tb_Spot_Updated tb_Deal := tb_Deal(); -- output collection

  l_To_Cy         FX_Rate.FX_To_Cy%type     := p_To_Cy;
  l_From_Cy       FX_Rate.FX_To_Cy%type     := p_From_Cy;
  l_Deal_Type     Trade_Deal.Deal_Type%type := p_Deal_Type;
  l_Latest        timestamp                 := p_Latest;

begin

  select  ty_Deal(td.Deal_ID,td.Deal_Type,l_To_Cy,fr.FX_Rate,0)
  bulk    collect into tb_Spot
  from    Trade_Deal td
  inner join FX_Rate fr on td.Deal_Type     = fr.Deal_Type
                        and fr.FX_From_Cy   = l_From_Cy
                        and fr.FX_Rate_Date <= l_Latest
  where   td.Deal_Type = l_Deal_Type;

  -- The following FORALL is combined with a BULK COLLECT
  forall i in 1..tb_Spot.count
     update Trade_Deal td
     set    td.USD_Amnt      =  td.GBP_Amnt*tb_Spot(i).Convert_Rate
     where  td.Deal_Type     =  tb_Spot(i).Deal_Type
     and    td.Deal_ID       =  tb_Spot(i).Deal_ID
     returning ty_Deal(td.Deal_ID
                      ,td.Deal_Type
                      ,l_To_Cy
                      ,tb_Spot(i).Convert_Rate
                      ,td.USD_Amnt)
     bulk collect into tb_Spot_Updated;

  p_tb_Deal := tb_Spot_Updated;
end Update_To_Currency;
/

Now for a test;

SQL> set serveroutput on
SQL> declare
  2    tb_Deals_Updated tb_Deal := tb_Deal();
  3  begin
  4
  5    Update_To_Currency ('SPOT'
  6                       ,'USD'
  7                       ,'GBP'
  8                       ,current_timestamp
  9                       ,tb_Deals_Updated);
 10
 11    -- Verify that the Collection contains only updated Deals
 12
 13    for i in 1..tb_Deals_Updated.count loop
 14      dbms_output.put_line(tb_Deals_Updated(i).Deal_ID
 15                 ||' '|| tb_Deals_Updated(i).Deal_Type
 16                 ||' '|| tb_Deals_Updated(i).Currency
 17                 ||' '|| tb_Deals_Updated(i).Convert_Rate
 18                 ||' '|| tb_Deals_Updated(i).Converted_Amnt);
 19    end loop;
 20
 21    --call other procs that take tb_Deal as a parameter type
 22    -- and pass tb_Deals_Updated as a parameter
 23
 24  end;
 25  /
1 SPOT USD 1.5757 15.757
4 SPOT USD 1.5757 34.6654

PL/SQL procedure successfully completed.

Finally, verify the TRADE_DEAL table was updated as well.

SQL> select deal_id,deal_type,usd_amnt
  2  from Trade_Deal
  3  where Deal_type = 'SPOT';

   DEAL_ID DEAL_TYPE    USD_AMNT
---------- ---------- ----------
         1 SPOT           15.757
         4 SPOT          34.6654