Is there an Oracle interface which tells you if the current transaction is the main transaction or an autonomous transaction withn PL/SQL context?
I know how to get the current transaction ID:
dbms_transaction.local_transaction_id
This would tell me if I'm within a transaction or not. But I need to further examine if the current transaction is the main transaction or if some code opened an autonomous transaction.
Use case / background:
During a test with utplsql there may be the need to differentiate between the main transaction and an autonomous transaction. For instance to see if the logic was correctly executed within an autonomous transaction. However, we are not sure yet about the test design in such cases so we want to see our options.
Surely, the autonomous transaction is only used during exception handling to create records which must not be rolled back.
When you start a PL/SQL block marked as autonomous, your current transaction pointer (v$session.taddr
) is NULL'd out even though the current transaction still exists and is still found in v$transaction
. When you do your first DML operation within that autonomous transaction layer, you now have two transaction entries in v$transaction
, but your transaction pointer (v$session.taddr
) points to only the last/current one. That makes sense, as v$session
which has only one row per session can only provide you with one transaction address for that one row, whereas you actually have two.
Fortunately, taddr/addr
is not the only link between these views. You also have ses_addr/saddr
going the other way. So you can use this to test if there is another transaction you own but which is not the current one (the transaction addresses don't match):
SELECT COUNT(*)
FROM v$transaction t,
v$session s
WHERE s.sid = SYS_CONTEXT('USERENV','SID')
AND t.ses_addr = s.saddr -- belongs to me
AND (s.taddr IS NULL OR s.taddr <> t.addr); -- but not my current transaction
If you get something other than 0, you are in an autonomous transaction.
You will of course need the SELECT ANY DICTIONARY
priv to see these views. Testing it out:
DECLARE
FUNCTION i_am_autonomous
RETURN varchar2
AS
var_exists integer;
BEGIN
SELECT COUNT(*)
INTO var_exists
FROM v$transaction t,
v$session s
WHERE t.ses_addr = s.saddr
AND s.sid = SYS_CONTEXT('USERENV','SID')
AND (s.taddr IS NULL OR s.taddr <> t.addr);
IF var_exists = 0
THEN
RETURN 'N';
ELSE
RETURN 'Y';
END IF;
END i_am_autonomous;
PROCEDURE ptest
AS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
dbms_output.put_line('inside autonomous but before inner transaction start:'||i_am_autonomous);
INSERT INTO tmp1 VALUES (2);
dbms_output.put_line('after inner transaction:'||i_am_autonomous);
ROLLBACK;
END ptest;
BEGIN
INSERT INTO tmp1 VALUES (1);
dbms_output.put_line('outer transaction before call:'||i_am_autonomous);
ptest;
dbms_output.put_line('outer transaction after call:'||i_am_autonomous);
ROLLBACK;
END;
Output:
outer transaction before call:N
inside autonomous but before inner transaction start:Y
after inner transaction:Y
outer transaction after call:N