CODEWITHSUNDEEP
oracle-databasefunctionplsqlprocedure
Karthik
Karthik

Reputation: 145

PLSQL: ORA-14552 Cannot perform DDL, Commit

In my PLSQL function, for writing loggers to log table i am calling a procedure (which contains commit statement). So i declared it as 

CREATE OR REPLACE PROCEDURE PR_LOGGER
( IN PARAMETERS-----)
IS Pragma Autonomous_transaction
BEGIN

--Insert statements

Commit;
END;

CALLING FUNCTION:

CREATE OR REPLACE FUNCTION MYFUNCTION
( --IN PARAMETERS )
BEGIN

---Some select statements
PR_LOGGER(logmessage);
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE tablename';
EXCEPTION WHEN OTHERS
   IF (SQLCODE !=942 ) THEN
   PR_LOGGER(SQLERRM);
   END IF;
END;
return NULL;
END;

So i understand if calling procedure or function contains commit, i will get ORA-14552 but even though i am handing it as Pragma Autonomous_transaction to avoid that error , i am still getting the same error. Any idea what i am doing wrong? Please advise.

Upvotes: 1

Views: 1586

Answers (1)

spencer7593
spencer7593

Reputation: 108470

The DROP TABLE statement causes an implicit commit.

The PRAGMA only applies to the execution of the PROCEDURE.

The DROP TABLE isn't executed in the context of the procedure. That's in the body, executed in the context, of the FUNCTION.

Upvotes: 3

Related Questions