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 br…

Accepted Answer

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 ?

PL/SQL Append_Values Hint gives error message

Solution

Problem