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