Monday, August 15, 2011

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

While I was calling a oracle function which initially insert data into a table, I got an error "ORA-14551: cannot perform a DML operation inside a query". I thought to share this post since it can be help to anyone who having the same error.

 Bellow is the oracle function:

CREATE OR REPLACE FUNCTION myfunction(ename IN VARCHAR) RETURN INTEGER AS eid INTEGER;
BEGIN
    INSERT INTO TEAM values(5,ename);
    SELECT id into eid from TEAM WHERE TEAM.name=ename;
    RETURN eid;
END;
/
And I called the function using the command,
SQL> select myfunction('test') from dual; 
which cause to give the above error.


Found that we cannot use 'select' to call functions which perform DML(insert. delete, update) operations.  It should be called using the 'call' command as bellow.

SQL> var myvar NUMBER;
SQL> call myfunction('test') into :myvar;  

Call completed.

SQL> print myvar;
MYVAR
----------
     5

2 comments:

  1. i was searching for this from a very long time..thanks for sharing this..:)

    ReplyDelete
  2. It's a copy paste with little modification from below mentioned url-
    http://www.dba-oracle.com/t_ora_14551_cannot_perform_a_dml_operation_inside_a_query.htm

    BTW it still gives me error in Oracle 9i

    ReplyDelete