Search notes:

ORA-14551: cannot perform a DML operation inside a query

A select statement cannot have an ordinary PL/SQL function which executes a DML statement in the selected columns. When trying to do so, the error ORA-14551: cannot perform a DML operation inside a query is thrown.

The following snippets first demonstrate how the error is caused and then proposes a way around it.

First, we need a function which executes a DML:

create or replace function exec_immediate(stmt clob) return number as
begin

    execute immediate stmt;
    return sql%rowcount;

end exec_immediate;
/

We also need a table which we try to populate with the function:

create table tq84_test(a number);

The following statement throws a ORA-14551: cannot perform a DML operation inside a query error message:

select
   exec_immediate('insert into tq84_test values(42)')
from
   dual;

In order to prevent the error, we compile the function with pragma autonomous_transaction …

create or replace function exec_immediate(stmt clob) return number as
    PRAGMA AUTONOMOUS_TRANSACTION;
begin

    execute immediate stmt;
    return sql%rowcount;

end exec_immediate;
/

… and try again. This time, it throws ORA-06519: active autonomous transaction detected and rolled back.

select
   exec_immediate('insert into tq84_test values(42)')
from
   dual;

So, we also commit the autonomous transaction within the function:

create or replace function exec_immediate(stmt clob) return number as
    PRAGMA AUTONOMOUS_TRANSACTION;
begin

    execute immediate stmt;
    COMMIT;                   -- important.
    return sql%rowcount;

end exec_immediate;
/

Finally, we can execute the DML in the function:

select
   exec_immediate('insert into tq84_test values(42)')
from
   dual;

Cleaning up:

drop function exec_immediate;
drop table    tq84_test purge;

ORA-14551: cannot perform a DML operation inside a query

See also