This was something I came across for the first time since working with Oracle. I guess I was assuming that PL/SQL variables wouldn't persist across an autonomous transaction, but they do. My mistake for thinking of an autonomous transaction as a completely separate database session, when it's 'independence' really only applies to SQL. Maybe most people already knew this, but I didn't find any specific references to it in an internet search, and no one in our shop expected it, so I thought it was worth a posting:
SQL> create or replace package store_variable_pg
2 as
3 l_variable varchar2(30);
4 end store_variable_pg;
5 /
Package created.
SQL> create or replace procedure autonomous_transaction
2 as
3 pragma autonomous_transaction;
4 begin
5 store_variable_pg.l_variable := 'autonomous_transaction';
6 end autonomous_transaction;
7 /
Procedure created.
SQL> create or replace procedure call_autonomous_transaction
2 as
3 begin
4 store_variable_pg.l_variable := 'call_autonomous_transaction';
5 dbms_output.put_line(store_variable_pg.l_variable);
6 autonomous_transaction();
7 dbms_output.put_line(store_variable_pg.l_variable);
8 end call_autonomous_transaction;
9 /
Procedure created.
SQL> set serveroutput on
SQL> begin
2 call_autonomous_transaction;
3 end;
4 /
call_autonomous_transaction
autonomous_transaction
PL/SQL procedure successfully completed.
I was expecting a separate copy of STORE_VARIABLE_PG to have been created during the execution of the AUTONOMOUS_TRANSACTION procedure, but instead only a single copy of STORE_VARIABLE_PG remains within scope for both procedures' executions.
Depending on how your application is setup, this could warrant a slight architectural change, so that you save package variables variables inside of a global temporary table rather than within package variables. (Before someone says it, we are not using a package as a storage spot for global variables as a general practice. We just noticed this effect, because we have some monitoring code placed in every procedure within a package body, which pushes/pops each procedure from a call stack, and that call stack is stored in a PL/SQL table setup in a separate package body).
--=Chuck
Oracle DB v11.2.0.3