This tutorial explains how to resolve the ORA-14552 error in Oracle. The full error message is "ORA-14552: Cannot Perform a DDL Commit or Rollback".
The following error usually occurs when running a PL/SQL program with a commit inside a trigger or calling a procedure or function inside a database trigger in Oracle.
ORA-14552: Cannot Perform a DDL Commit or Rollback Inside a Query or DML
As per Oracle, the following is the cause of this error and the recommended action suggested by Oracle is:
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.
Resolve ORA-14552 Error in Oracle
To resolve this error, add the pragma autonomous_transaction command in the declaration section of your PL/SQL code. Below is an example:
CREATE OR REPLACE PROCEDURE yourProc is v_loc number; pragma autonomous_transaction; begin -- more code end;
Using this you can resolve the error ORA-14552 and can use the
commit statement inside your PL/SQL code: