Question: I am getting an
"ORA-14552: cannot perform a DDL, commit or rollback inside a query
or DML" error and I want to make my function into an autonomous
transaction with the "pragma autonomous transaction" syntax:
create or replace function
rebuildSequence
return number
as
l_csmsgid1 pls_integer;
pragma
autonomous_transaction;
begin . . .
I got the rid of the ORA-14552 error by making
it an autonomous transaction, but I don't understand what an
autonomous transaction is and why this must be an autonomous
transaction.
Can you please explain the "pragma autonomous
transaction," when to use an autonomous transaction and why an
autonomous transaction stops the ORA-14552 error?
Answer: An
autonomous transaction is code which is independent of the
transaction that spawned it, allowing the parent transaction to
continue regardless of the success or failure of the autonomous
transaction.
Autonomous transactions are useful for tasks that you want
to execute regardless of the status of the parent transaction.
Consider an error log table, where you want to save error messages
generated by a transaction. You will need to roll back the
transaction which caused the error, but you don't want the error
code to be lost in the rollback. When the error is logged and
committed in an autonomous transaction and the parent transaction is
rolled back, the changes committed by the autonomous transaction are
preserved.
Moving your code to the autonomous transaction removes the
ORA-14552 error because you are creating a whole new transaction.
When a procedure defined with the pragma autonomous transaction
compiler directive is called, the calling transaction suspends
untilt he autonomous transaction is completed.
 |
|
Get the Complete
Oracle SQL Tuning Information
The landmark book
"Advanced Oracle
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
buy it
for 30% off directly from the publisher.
|
