oracle ORA-14551: 无法在查询中执行 DML 操作
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3280610/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ORA-14551: cannot perform a DML operation inside a query
提问by learn_plsql
I have the following inside a packageand it is giving me an error:
我在 a 中有以下内容package，它给了我一个错误：
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-14551error?
我怎样才能避免这个ORA-14551错误？
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.
仅供参考，我没有将整个代码粘贴到循环中。基本上在循环内部，我在 GTT 中输入内容，从 GTT 中删除内容，然后从 GTT 中选择内容并将其附加到光标。
回答by APC
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.
错误的含义很清楚：如果我们从 SELECT 语句调用一个函数，它就不能执行 DML 语句，即 INSERT、UPDATE 或 DELETE，或者实际上是 DDL 语句。
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.
现在，您发布的代码片段包含对 PIPE ROW 的调用，因此很明显您将其称为 SELECT * FROM TABLE()。但是它包括 DELETE 和 INSERT 语句，很明显它违反了 SELECT 语句中函数所需的纯度级别。
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.
因此，您需要删除那些 DML 语句。您正在使用它们来填充全局临时表，但这是个好消息。您没有包含任何实际使用 GTT 的代码，因此很难确定，但使用 GTT 通常是不必要的。有了更多详细信息，我们可以建议解决方法。
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.
为完整起见，可以在 SELECT 语句中调用的函数中包含 DML 和 DDL 语句。解决方法是使用 AUTONOMOUS_TRANSACTION pragma。这很少是一个好主意，并且在这种情况下肯定无济于事。因为事务是自治的，所以它所做的更改对调用事务是不可见的。在这种情况下，这意味着该函数无法看到 GTT 中删除或插入的结果。
回答by devio
The error means you are SELECTing from a function which modifies data (DELETE, INSERT in your case).
该错误意味着您正在从修改数据的函数中进行选择（在您的情况下为 DELETE、INSERT）。
Remove the data modification statements from that function into a separate SP, if you need that functionality. (I guess I don't understand from the code snippet why you want to delete and insert inside the loop)
如果需要该功能，则将该函数中的数据修改语句删除到单独的 SP 中。（我想我从代码片段中不明白为什么要在循环中删除和插入）
