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.
Wednesday, 23 November 2011
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.
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
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
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
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
Subscribe to:
Posts (Atom)