PL/SQL Append_Values Hint gives error message

Question

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_Values hint as in the following example:

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?

Answer 1

We cannot execute 2 DML transactions in a table without committing the first transaction. And hence this error will be thrown.

SO, commit your previous transaction in that table and continue the second transaction.

Answer 2

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:

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 ?

Answer 3

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;