Fact about ORA-14551

You might have come across below error.

ERROR at line 1:
ORA-14551: cannot perform a DML operation inside a query
ORA-06512: at "xxxxx", line xx
ORA-06512: at line 1

Why?

This error raises in following scenario

You have Database function (say myFn) that has DML operation such as Update/Delete/Insert

and you are query some thing like below

Select id, myFn(id) from customer.

Here you are performing a Select operation on the myFn, which internally performs DML operation.

How do you avoid?

If you are testing this function,

simply test as below
var :rtn varchar2(10); -- assuming function retruns varchar
call myFn(id) into :rtn;
print :rtn;

− OR −

set serveroutput on
begin
dbms_output.put_line(myFn(20));
end;

− OR −

if you are not testing, but rather you want to do select operation on the function, define function as autonomous transaction.

i.e. Put    PRAGMA AUTONOMOUS_TRANSACTION; in the declaration section of the function

Read Full Post »