How to use DML Statements in a Function

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:

SELECT fun2(7499) FROM dual;