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