Using autonomous transactions from within SQL

Ever since Oracle 7.3, you have been able to call your own functions from within SQL—provided that you follow the rules. The main one is this: you are not allowed to update the database. And you certainly can't save or cancel changes from within the function.

With the autonomous transaction feature, however, the picture changes a good deal. An autonomous transaction program never violates the two database-related purity levels, RNDS (reads no database state) and WNDS (writes no database state), even if the program actually does read from or write to the database. This is possible because those purity levels or constraints apply to the SQL statement (which, in this case, is the main transaction), but an autonomous transaction's DML actions never affect the main transaction.

So as long as you define a program to be an autonomous transaction, you can also call it directly or indirectly in a SQL statement. Of course, if your program cannot assert another purity level, such as WNPS (writes no package state), you may be restricted from calling that program in certain parts of the SQL statement, such as the WHERE clause.

As an example, suppose that I want to keep a trace of all the rows that have been touched by a query. I create this table:

/* File on web: trcfunc.sql */CREATE TABLE query_trace ( table_name VARCHAR2(30), rowid_info ROWID, queried_by VARCHAR2(30), queried_at DATE );

I then create this simple function to perform the audit:

CREATE OR REPLACE FUNCTION traceit ( tab IN VARCHAR2, rowid_in IN ROWID) RETURN INTEGERISBEGIN INSERT INTO query_trace VALUES (tab, rowid_in, USER, SYSDATE); RETURN 0;END;

When I try to use this function inside a query, I get the expected error:

SQL> select ename, traceit ('emp', rowid) from emp; *ERROR at line 1:ORA-14551: cannot perform a DML operation inside a query

However, if I now transform traceit into an autonomous transaction by adding the pragma (and committing my results before the RETURN statement!), the results are very different. My query works, and the query_trace table is filled:

SQL> SELECT ename, traceit ('emp', ROWID) FROM emp; ENAME TRACEIT('EMP',ROWID)---------- --------------------KING 0...MILLER 014 rows selected. SQL> SELECT table_name, rowid_info, queried_by,2 TO_CHAR (queried_at, 'HH:MI:SS') queried_at 3 FROM query_trace; TABLE_NAME ROWID_INFO QUERIED_BY QUERIED_AT---------- ------------------ ---------- ----------emp AAADEPAACAAAAg0AAA SCOTT 05:32:54...emp AAADEPAACAAAAg0AAN SCOTT 05:36:50

You have other options when it comes to tracing queries: you can write to the screen with the DBMS_OUTPUT built-in package or send information to a pipe with DBMS_PIPE. Now that autonomous transactions are available, if you do want to send information to a database table (or delete rows, update data, etc.), you can take that route instead, but be sure to carefully analyze the overhead of this approach.