While it is certainly very easy to add the autonomous transaction pragma to your code, there are some rules and restrictions on the use of this feature.
You can make only a top-level anonymous block an autonomous transaction. This will work:
DECLARE PRAGMA AUTONOMOUS_TRANSACTION; myempno NUMBER; BEGIN INSERT INTO emp VALUES (myempno,...); COMMIT;END;whereas this construction:
DECLARE myempno NUMBER;BEGIN DECLARE PRAGMA AUTONOMOUS_TRANSACTION; BEGIN INSERT INTO emp VALUES (myempno,...); COMMIT; END;END;results in this error:
PLS-00710: PRAGMA AUTONOMOUS_TRANSACTION cannot be declared hereIf an autonomous transaction attempts to access a resource held by the main transaction (which has been suspended until the autonomous routine exits), a deadlock can occur in your program. Here is a simple example to demonstrate the problem. I create a procedure to perform an update, and then call it after having already updated all rows:
/* File on web: autondlock.sql */ CREATE OR REPLACE PROCEDURE update_salary (dept_in IN NUMBER) IS PRAGMA AUTONOMOUS_TRANSACTION; CURSOR myemps IS SELECT empno FROM emp WHERE deptno = dept_in FOR UPDATE NOWAIT; BEGIN FOR rec IN myemps LOOP UPDATE emp SET sal = sal * 2 WHERE empno = rec.empno; END LOOP; COMMIT; END; BEGIN UPDATE emp SET sal = sal * 2; update_salary (10);END;The results are not pretty:
ERROR at line 1:ORA-00054: resource busy and acquire with NOWAIT specifiedYou cannot mark all subprograms in a package (or all methods in an object type) as autonomous with a single PRAGMA declaration. You must indicate autonomous transactions explicitly in each program. One consequence of this rule is that you cannot tell by looking at the package specification which (if any) programs will run as autonomous transactions.
To exit without errors from an autonomous transaction program, you must perform an explicit commit or rollback. If the program (or any program called by it) has transactions pending, the runtime engine will raise the exception shown belowand then will roll back those uncommitted transactions.
ORA-06519: active autonomous transaction detected and rolled backThe COMMIT and ROLLBACK statements end the active autonomous transaction, but they do not force the termination of the autonomous routine. You can, in fact, have multiple COMMIT and/or ROLLBACK statements inside your autonomous block.
You can roll back only to savepoints marked in the current transaction. When you are in an autonomous transaction, therefore, you cannot roll back to a savepoint set in the main transaction. If you try to do so, the runtime engine will raise this exception:
ORA-01086: savepoint 'your savepoint' never establishedThe TRANSACTIONS parameter in the Oracle initialization file specifies the maximum number of transactions allowed concurrently in a session. If you use lots of autonomous transaction programs in your application, you might exceed this limit, in which case you will see the following exception:
ORA-01574: maximum number of concurrent transactions exceededIn this case, increase the value for TRANSACTIONS. The default value is 75.
