How to Fix ORA-14551: Cannot Perform a DML Operation Inside a Query in Oracle

ORA-14551 is raised when a function called from a SELECT statement tries to insert, update, or delete rows. A query must not change data while it reads it, so Oracle stops the function at its first DML statement. Typical cases are functions that write an audit or log row. Call such functions from PL/SQL instead, or move the DML out of the function.

Reproduce the Error

Example:

create table demo_audit (logged_at timestamp default systimestamp, note varchar2(100));
create or replace function demo_logged_fare (p_route varchar2) return number is
begin
  insert into demo_audit (note) values ('fare read for ' || p_route);
  return 120;
end;
/
select demo_logged_fare('DEL-BOM') as fare from dual;

Output:

Table DEMO_AUDIT created.

Function DEMO_LOGGED_FARE compiled

Error starting at line : 8
In command -
select demo_logged_fare('DEL-BOM') as fare from dual
Error at Command Line : 8 Column : 8
Error report -
SQL Error: ORA-14551: cannot perform a DML operation inside a query
ORA-06512: at "NIMBUS.DEMO_LOGGED_FARE", line 3

ORA-06512 points to line 3 of the function, the INSERT.

Fix: Call the Function from PL/SQL

Example:

set serveroutput on
-- call the function from PL/SQL, where DML is allowed
declare
  v_fare number;
begin
  v_fare := demo_logged_fare('DEL-BOM');
  dbms_output.put_line('Fare: ' || v_fare);
end;
/
select note from demo_audit;
rollback;

Output:

Fare: 120

PL/SQL procedure successfully completed.

NOTE
________________________
fare read for DEL-BOM

Rollback complete.

An assignment in PL/SQL is not a query, so the INSERT is allowed, and it is part of the caller's transaction.

Other Options

• Split the function: one that only returns the value, for queries, and a procedure that writes the row.
• Write the log with an autonomous transaction (PRAGMA AUTONOMOUS_TRANSACTION, with a COMMIT in the routine). The row is then kept even if the query's transaction rolls back, and is written once per call, which for a query can be once per row.
• Functions used in DML statements, for example in UPDATE ... SET, raise the related ORA-04091 when they read or change the table being modified.

Related Guides

• Oracle Autonomous Transaction Example
• How to Fix ORA-06504: PL/SQL: Return Types of Result Set Variables or Query Do Not Match in Oracle

Conclusion

ORA-14551 means a function called from a query tried to change data. Call it from PL/SQL, separate the read from the write, or log with an autonomous transaction when the row must be written regardless.