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
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
Can you please explain the "pragma autonomous
transaction," when to use an autonomous transaction and why an
autonomous transaction stops the ORA-14552 error?
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
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
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
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
for 30% off directly from the publisher.