Tuesday, March 13, 2012

Cannot perform a DML operation inside a Query in Functions

ORA-14551: cannot perform a DML operation inside a query

CREATE OR REPLACE FUNCTION vamsi_f(ename IN VARCHAR) RETURN INTEGER
AS empid INTEGER;
BEGIN
INSERT INTO EMP_BK (empno, ename) values(5,ename);
SELECT empno into empid from EMP_BK WHERE EMP_BK.ENAME=ename;
RETURN empid;
END;

I called the function using the command,
sql> select vamsi_f('vamsi') from dual;

which cause to give the bwlow error

ORA-14551: cannot perform a DML operation inside a query
ORA-06512: at "TEST.VAMSI_F", line 4

DML(insert. delete, update) operations should be called using the 'call' command as bellow.

sql> var myvar NUMBER;
sql> call vamsi_f('vamsi') into :myvar;
sql> print myvar

MYVAR STRING = 5