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;