How to use DML Statements in a Function

You can use DML inside a PL/SQL function.However, the function can only be called from PL/SQL, not from SQL.

Generally you cannot do a dml in function and call the function in select.. The reason is "Select statement should not alter any data".. "SELECT" is not coming under DML.. but again by using Pragma Autonomous Transaction you can do.. The reason is when u use Pragma Autonomous Transaction, the DML Part will be executed in a Seperate Session (not from the Session where u issued select call).. but its never a god idea... Dont use DML in functions... Use Procedures

We can not place any DML statement inside a function simply and also a function with a DML statement cannot be used inside a SELECT query.
Here is a small example supporting the above statement:

CREATE OR REPLACE FUNCTION fun1(i_empno IN NUMBER) RETURN NUMBER AS i_count NUMBER; BEGIN -- DELETE FROM emp WHERE empno = i_empno; -- i_count:=sql%rowcount; -- RETURN i_count; END; /

If we try to run the above function in a select query, the oracle engine throws the error as shown below:

SQL> SELECT fun1(1) FROM dual;   ORA-14551: cannot perform a DML operation inside a query ORA-06512: at "SYSTEM.FUN1", line 6 14551. 00000 - "cannot perform a DML operation inside a query " *Cause: DML operation LIKE INSERT, UPDATE, DELETE OR select-for-UPDATE cannot be performed inside a query OR UNDER a PDML slave. *Action: Ensure that the offending DML operation IS NOT performed OR USE an autonomous TRANSACTION TO perform the DML operation WITHIN the query OR PDML slave.

But we can run the above DML statement based function in an anonymous block as shown below:

DECLARE a NUMBER; BEGIN a:=fun1(7369); dbms_output.put_line(a); END; /

To use a DML statement inside a Function and also to use that function inside a SELECT query, we have to use PRAGMA AUTONOMOUS_TRANSACTION inside the function.

CREATE OR REPLACE FUNCTION fun2( i_empno IN NUMBER) RETURN NUMBER AS pragma autonomous_transaction; i_count NUMBER; BEGIN DELETE FROM emp WHERE empno=i_empno; i_count:=sql%rowcount; COMMIT; RETURN i_count; END; /

You can use the above function ‘fun2’ in a select query as shown below: