ORA-14551:无法在查询中执行DML操作 [英] ORA-14551: cannot perform a DML operation inside a query

问题描述

我在package中包含以下内容，这给我一个错误:

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

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

代码是:

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);

如何避免出现此ORA-14551错误?

仅供参考，我还没有将整个代码粘贴到循环中.基本上在循环内，我在GTT中输入内容，从GTT中删除内容，然后从GTT中选择内容并将其附加到光标上.

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.

推荐答案

错误的含义非常清楚:如果我们从SELECT语句调用函数，则它无法执行DML语句，即INSERT，UPDATE或DELETE，或者确实，DDL语句可以实现这一点.

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.

现在，您发布的代码片段包含对PIPE ROW的调用，因此，很显然，您将其称为SELECT * FROM TABLE().但是它包含DELETE和INSERT语句，因此很明显，它不符合SELECT语句中函数所要求的纯度级别.

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.

因此，您需要删除那些DML语句.您正在使用它们填充全局临时表，但这是个好消息.您尚未包含任何实际使用GTT的代码，因此很难确定，但是通常不需要使用GTT.有关更多详细信息，我们可以建议解决方法.

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?

为了完整起见，可以在SELECT语句中调用的函数中包含DML和DDL语句.解决方法是使用AUTONOMOUS_TRANSACTION编译指示.这很少是一个好主意，在这种情况下当然无济于事.因为事务是自主的，所以它所做的更改对于调用事务是不可见的.在这种情况下，意味着该功能无法看到在GTT中删除或插入的结果.

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.

这篇关于ORA-14551:无法在查询中执行DML操作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！