关于"cannot perform a DDL, commit or rollback inside a query or DML" 错误

error scenario:

update a set field1 = functionA() where ...

function functionA return varchar2 as

begin

...

 -- may be

 raise_application_error(-20000,'TEST');

...

EXCEPTION

 WHEN OTHERS THEN

　　log_to_table(...)

　　Commit;

end functionA;

Most of the time, functionA will not cause any problem. It log into log table when error happens inside of it. And commit the log.

But , Some time it will cause issue when functionA was called from an update statement(DML statement) as a value passed to "set" clause.

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

Resolve solution:

1, in this situation we can apply "autonomous transaction" to log_to_table, and remove the "Commit" . this solution will cause some performance loss.

2, remove "commit". "commit" transaction after "update..."(DML).

转载于:https://www.cnblogs.com/wzc998/archive/2011/08/16/2140199.html