6

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?

Hash
  • 4,647
  • 5
  • 21
  • 39
user3312037
  • 61
  • 1
  • 1
  • 2
  • Where are the rows coming from? Do you really just want to insert the numbers 1 to 100? Or are you really pulling the data from some source? – Justin Cave Feb 14 '14 at 23:16
  • I am given a table with individuals as well as the start dates and end dates that the individuals enrolled in a program. For the output table, I need a row for each month/year that the individual was enrolled in the program. So if an individual has a start date of 12/21/2012 and an end date of 4/10/2013, I would need five records for the individual (a record for 12/2012, 01/2013, 02/2013, 03/2013, and 04/2013). My PL/SQL code without the APPEND_VALUES hint works well for small tables, but my table has about one million rows. – user3312037 Feb 16 '14 at 00:26
  • 1
    Rather do not use it. Especially from PL/SQL it should be possible to rewrite the code into pure SQL "insert /* +APPEND */ ..select". Moreover this solution does not scale because only one session can append to a table at the same time. Moreover direct path write is used only under some circumstances, like no FKs on a table. If you really want do speedup your ETL use `ALTER SESSION FORCE PARALLEL DML`. – ibre5041 Oct 05 '16 at 10:58

3 Answers3

4

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 ?

Dmitry Nikiforov
  • 2,988
  • 13
  • 12
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;
Shailesh Gupta
  • 31
  • 1
  • 1
    Yep, +1 - it's not the Append hint that causes the error - it's the fact Oracle balks at modifying a table again after just modifying it. – vapcguy May 17 '17 at 22:35
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.

user8758015
  • 11
  • 1