Differentiate between main transaction and autonomous transaction?

Question

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.

Answer 1

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