ORA-06519: active autonomous transaction detected and rolled back
create or replace function exec_immediate(stmt clob) return clob as
pragma autonomous_transaction;
begin
execute immediate stmt;
return stmt;
end exec_immediate;
/
create table tq84_test(a number);
The following statement throws ORA-06519: active autonomous transaction detected and rolled back:
select
exec_immediate('insert into tq84_test values(42)')
from
dual;
In order to prevent this error, an explicit commit (or rollback) is required in the function:
create or replace function exec_immediate(stmt clob) return clob as
pragma autonomous_transaction;
begin
execute immediate stmt;
COMMIT;
return stmt;
end exec_immediate;
/