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&gt; 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;

How to use DML Statements in a Function

For Further Reading