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