cannot perform DML operation inside a query

Question

I cannot convince why I can't add DML operation inside Oracle Function especially inside cursor loop. I feel Oracle don't support DML operation inside cursor loop.

How can I do If I need to insert into table inside cursor loop? Create new store procedure inside it or something else?

Error Message : cannot perform DML operation inside a query

Here is my function,

CREATE OR REPLACE FUNCTION TEST_FUNC(U_ID IN VARCHAR2)
RETURN VARCHAR2
IS
  V_MESSAGE VARCHAR2(30);
  CURSOR C_PERSON (V_ID VARCHAR2) IS
         SELECT NAME_UPPER
         FROM TBL_PERSON
         WHERE NAME_UPPER = V_ID;                  
BEGIN
   FOR C_PERSON_CURSOR IN C_PERSON(U_ID) 
   LOOP
       INSERT INTO TMP_PERSON(NAME) VALUES (C_PERSON_CURSOR.NAME_UPPER);
   END LOOP;

   RETURN V_MESSAGE;

EXCEPTION
WHEN OTHERS THEN
    raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END;

Answer 1

You can use DML inside a PL/SQL function - no problem. However, the function can only be called from PL/SQL, not from SQL - i.e. it can be called like this:

declare
   l_message varchar2(30);
begin
   l_message := test_func('123');
end;

... but not like this:

select test_func(empno) from emp;

That leads to the error message you posted.

Many people (including me) don't like functions that have "side effects" like this, but that is a matter of best practice and standards, not a technical issue.