oracle PL/SQL Append_Values 提示给出错误信息
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21791090/
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
PL/SQL Append_Values Hint gives error message
提问by user3312037
I am having trouble doing a large number of inserts into an Oracle table using PL/SQL. My query goes row-by-row and for each row the query makes a calculation to determine the number of rows it needs to insert into the another table. The conventional inserts work but the code takes a long time to run for a large number of rows. To speed up the inserts I tried to use the Append_Valueshint as in the following example:
我在使用 PL/SQL 对 Oracle 表进行大量插入时遇到问题。我的查询逐行进行，对于每一行，查询都会进行计算以确定需要插入到另一个表中的行数。传统的插入工作但代码需要很长时间才能运行大量行。为了加速插入，我尝试使用Append_Values提示，如下例所示：
BEGIN
FOR iter in 1..100 LOOP
INSERT /*+ APPEND_VALUES*/ INTO test_append_value_hint values (iter);
END LOOP;
END;
I get the following error message when doing this:
执行此操作时，我收到以下错误消息：
ORA-12838: cannot read/modify an object after modifying it in parallel
ORA-06512: at line 3
12838. 00000 - "cannot read/modify an object after modifying it in parallel"
*Cause: Within the same transaction, an attempt was made to add read or
modification statements on a table after it had been modified in parallel
or with direct load. This is not permitted.
*Action: Rewrite the transaction, or break it up into two transactions
one containing the initial modification and the second containing the
parallel modification operation.
Does anyone have ideas of how to make this code work, or how to quickly insert large numbers of rows into another table?
有没有人知道如何使这段代码工作，或者如何快速将大量行插入另一个表？
回答by Dmitry Nikiforov
You get this error because every your INSERT executes as a separate DML statement. Oracle prevents read/write on the table where data were added using direct path insert until commit. Technically you can use PL/SQL collections and FORALL instead:
您会收到此错误，因为您的每个 INSERT 都作为单独的 DML 语句执行。Oracle 防止对使用直接路径插入添加数据的表进行读/写，直到提交。从技术上讲，您可以改用 PL/SQL 集合和 FORALL：
SQL> declare
2 type array_t is table of number index by pls_integer;
3 a_t array_t;
4 begin
5 for i in 1..100 loop
6 a_t(i) := i;
7 end loop;
8 forall i in 1..100
9 insert /*+ append_values */ into t values (a_t(i));
10 end;
11 /
But the question Justin asked is in action - where are your data coming from and why can't you use usual INSERT /*+ append */ INTO ... SELECT FROM approach ?
但是贾斯汀提出的问题正在起作用 - 您的数据来自哪里，为什么不能使用通常的 INSERT /*+ append */ INTO ... SELECT FROM 方法？
回答by Shailesh Gupta
Hi Request you to use commit after insert as below:
嗨，请求您在插入后使用提交，如下所示：
BEGIN
FOR iter in 1..100 LOOP
INSERT /*+ APPEND_VALUES*/ INTO test_append_value_hint values (iter);
COMMIT;
END LOOP;
END;
回答by user8758015
We cannot execute 2 DML transactions in a table without committing the first transaction. And hence this error will be thrown.
如果不提交第一个事务，我们就不能在一个表中执行 2 个 DML 事务。因此将抛出此错误。
SO, commit your previous transaction in that table and continue the second transaction.
因此，在该表中提交您之前的事务并继续第二个事务。
