Wednesday, September 8, 2021

ORA-14552: Cannot Perform a DDL Commit or Rollback inside a query or DML

You will receive this error when you use COMMIT or ROLLBACK and make sure you don’t use DML in a function to avoid this error.

In case if you need to use COMMIT or ROLLBACK then change your function as autonomous transaction by using “PRAGMA AUTONOMOUS_TRANSACTION" in your function

For Ex:-
CREATE OR REPLACE FUNCTION Function_Autonomous
return number
as
v_number number;
pragma autonomous_transaction;
BEGIN
…………
END;
/

14552, 00000, "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.

Thanks & Regards