Transactions - Oracle Database Transactions and Locking Revealed - page 103

This feature has now been exposed for all to use. However, I have found that the legitimate real-world use of
autonomous transactions is very limited . Time after time, I see them used as a workaround to such problems as a
mutating table constraint in a trigger. This almost always leads to data integrity issues, however, since the cause of the
mutating table is an attempt to read the table upon which the trigger is firing. Well, by using an autonomous transaction
you can query the table, but you are querying the table now without being able to see your changes (which is what the
mutating table constraint was trying to do in the first place; the table is in the middle of a modification, so query results
would be inconsistent). Any decisions you make based on a query from that trigger would be questionable—you are
reading “old” data at that point in time.
A potentially valid use for an autonomous transaction is in custom auditing, but I stress the words “potentially
valid.” There are more efficient ways to audit information in the database than via a custom-written trigger. For
example, you can use the DBMS_FGA package or just the AUDIT command itself.
A question that application developers often pose to me is, “How can I log errors in my PL/SQL routines in a
manner that will persist, even when my PL/SQL routines' work is rolled back?” Earlier, we described how PL/SQL
statements are atomic —they either completely succeed or completely fail. If we logged an error in our PL/SQL
routines, by default our logged error information would roll back when Oracle rolled back our statement. Autonomous
transactions allow us to change that behavior, to have our error logging information persist even while the rest of the
partial work is rolled back.
Let's start by setting up a simple error logging table to use; we'll record the timestamp of the error, the error
message, and the PL/SQL error stack (for pinpointing where the error emanated from):
EODA@ORA12CR1> create table error_log
2 ( ts timestamp,
3 err1 clob,
4 err2 clob )
5 /
Table created.
Now we need the PL/SQL routine to log errors into this table. We can use this small example:
EODA@ORA12CR1> create or replace
2 procedure log_error
3 ( p_err1 in varchar2, p_err2 in varchar2 )
4 as
5 pragma autonomous_transaction;
6 begin
7 insert into error_log( ts, err1, err2 )
8 values ( systimestamp, p_err1, p_err2 );
9 commit;
10 end;
11 /
Procedure created.
The “magic” of this routine is on line 5 where we used the pragma autonomous_transaction directive to inform
PL/SQL that we want this subroutine to start a new transaction, perform some work in it, and commit it—without
affecting any other transaction currently in process. The COMMIT on line 9 can affect only the SQL performed by this
LOG_ERROR procedure.