Question: : I am running a PL/SQL function
with a commit, and I am getting this ORA-14552 error:
ORA-14552: Cannot Perform a DDL Commit or
Rollback Inside a Query or DML
How do I avoid this ORA-14552 error? Is it safe to make my
function contain an autonomous transaction?
Answer: : To diagnose the ORA-14552 error, we
use the
oerr utility to look up the
ORA-14552 error message, cause and action:
ORA-14552: cannot perform a
DDL, commit or rollback inside a query or DML
Cause: DDL operations like creation tables, views
etc. and transaction control statements such as commit/rollback
cannot be performed inside a query or a DML statement.
Action: Ensure that the offending operation is
not performed or use autonomous transactions to perform the
operation within the query/DML operation.
In your case, you are getting the ORA-14552 error because you are
issuing a commit.
If you must use a commit, you can avoid the ORA-14452 error
by making your function into an autonomous transaction using "PRAGMA
AUTONOMOUS_TRANSACTION;" in the function:
CREATE OR REPLACE
FUNCTION rebuildSequence
return number
as
v_loc number;
pragma
autonomous_transaction;
begin . . .
See these notes on using
autonomous transactions.
 |
|
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.
|
