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

Question

I have the following inside a package and it is giving me an error:

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

Code is:

DECLARE 
    CURSOR F IS
        SELECT ROLE_ID 
        FROM ROLE 
        WHERE GROUP = 3 
        ORDER BY GROUP ASC;

BEGIN
FOR R IN F LOOP

DELETE FROM my_gtt_1;
COMMIT;

 INSERT INTO my_gtt_1
  ( USER, role, code, status )
(SELECT 
 trim(r.user), r.role, r.code, MAX(status_id)
FROM 
  table1 r, 
  tabl2 c
WHERE 
      r.role = R.role
  AND r.code IS NOT NULL
  AND c.group = 3
  GROUP BY 
  r.user, r.role, r.code);

  SELECT c.role,
                  c.subgroup,
                  c.subgroup_desc,
                  v_meb_cnt
                  INTO record_type
           FROM   ROLE c
           WHERE c.group = '3' and R.role = '19'
           GROUP BY c.role,c.subgroup,c.subgroup_desc;

  PIPE ROW (record_type);



END LOOP;

END;

I call the package like this in one of my procedures...:

OPEN cv_1 for SELECT * FROM TABLE(my_package.my_func);

how can I avoid this ORA-14551 error?

FYI I have not pasted the entire code inside the loop. Basically inside the loop I am entering stuff in GTT, deleting stuff from GTT and then selecting stuff from GTT and appending it to a cursor.

Solution

The meaning of the error is quite clear: if we call a function from a SELECT statement it cannot execute DML statements, that is INSERT, UPDATE or DELETE, or indeed DDL statements come to that.

Now, the snippet of code you have posted contains a call to PIPE ROW, so plainly you are calling this as SELECT * FROM TABLE(). But it includes DELETE and INSERT statements so clearly it falls foul of the purity levels required for functions in SELECT statements.

So, you need to remove those DML statements. You are using them to populate a global temporary table, but this is good news. You haven't include any code which actually uses the GTT so it is difficult to be sure, but using GTTs is often unnecessary. With more details we can suggest workarounds.

Is this related to this other question of yours? If so, did you follow my advice to check that answer I had given to a similar question?

---

For the sake of completeness, it is possible to include DML and DDL statements in a function called in a SELECT statement. The workaround is to use the AUTONOMOUS_TRANSACTION pragma. This is rarely a good idea, and certainly wouldn't help in this scenario. Because the transaction is autonomous the changes it makes are invisible to the calling transaction. Meaning in this case that the function cannot see the outcome of the deletion or insertion in the GTT.