Using PRAGMA AUTONOMOUS_TRANSACTION in a Function

Here’s a quick example of how to use the AUTONOMOUS_TRANSACTION pragma in a function:

create table list_of_function_calls
( 
    text varchar2(100)
   , ts timestamp with time zone
);

create or replace function get_random_number
  return number
is
  pragma autonomous_transaction;
begin
   -- Everything in this function happens in a separate 
   -- database transaction.
   insert into list_of_function_calls (text, ts)
   values ('I was called!', systimestamp);
   commit;

    -- Number randomly chosen from roll the dice
    return 2;
end get_random_number;
/

If you use the AUTONOMOUS_TRANSACTION pragma in Pl/SQL, it creates a separate transaction from the transaction you are currently working in. Because we included it in our function definition, everything in the body of the function happens in this separate transaction. Therefore, you can commit or rollback within the function, and it will not affect the calling transaction.

In our example above, I could have a scenario like this:

create table random_numbers
(
  rand_num number
);

insert into random_numbers (rand_num)
select get_random_number
from dual
connect by level <= 1000;

rollback;

In the above scenario, the rollback on the very last line would roll back the 1000 rows that I inserted into the RANDOM_NUMBERS table. But, our committed autonomous transaction would preserve the records inserted into the LIST_OF_FUNCTION_CALLS table.

SQL> select *
  2  from random_numbers;

no rows selected

SQL> select count(*)
  2  from list_of_function_calls;

  COUNT(*)
----------
      1000

If you removed the pragma, then the rollback would roll back both the insertion into RANDOM_NUMBERS and the insertion into LIST_OF_FUNCTION_CALLS.

Only Use PRAGMA AUTONOMOUS_TRANASCTION For Logging Purposes

Tom Kyte says on the Oracle AskTom site that you should only use this pragma for logging. In fact, if you are considering using an autonomous transaction aside from logging, you probably need to rethink your database design.

Be extremely careful not to abuse this pragma, because you should only use it for logging. If you to need to use an AUTONOMOUS_TRANSACTION pragma aside from logging purposes, then your database design may be flawed.

Hopefully this was helpful to you. Thanks for reading!

Free Oracle SQL Tuning Guide

Check out my FREE guide! 7 SQL Tuning Secrets You Can Use Immediately, Even If You’ve Never Tuned a Query In Your Life!

Get it here: tuningsql.com/secrets