ORA-12838: Object cannot be read/changed after parallel change error in DML statement inside trigger

Issue

This Content is from Stack Overflow. Question asked by DataGeek

I have a compound trigger which three DML statements in it. And one of my job is failing because of this trigger with the error “ORA-12838: Object cannot be read/changed after parallel change error”. Since commit cant be done in a trigger, what will be the best option to fix this issue. The error occurs after the insert statement.

CREATE OR REPLACE TRIGGER dup_ct
FOR INSERT ON test1
COMPOUND TRIGGER

    TYPE a_records IS TABLE OF test1%ROWTYPE
                 INDEX BY PLS_INTEGER;
    
    new_records a_records := a_records ();
    rows_inserted   PLS_INTEGER := 0;
    v_dup       PLS_INTEGER;

  BEFORE EACH ROW IS
  BEGIN
    rows_inserted := rows_inserted + 1;
    new_records (rows_inserted).class_id  := :NEW.class_id;
    new_records (rows_inserted).mark      := :NEW.mark;
    new_records (rows_inserted).std       := :NEW.std;
    new_records (rows_inserted).res_date  := :NEW.res_date;
    new_records (rows_inserted).exam_date := :NEW.exam_date;
  END BEFORE EACH ROW;

  AFTER STATEMENT IS
  BEGIN
    FOR j IN 1 .. rows_inserted
    LOOP
      SELECT COUNT (*)
      INTO   v_dup
          FROM   test1
      WHERE  mark      = new_records (j).mark
          AND    std       = new_records (j).std
          AND    res_date  = new_records (j).res_Date
      AND    exam_date = new_records (j).exam_date;

      IF v_dup > 1
          THEN
        INSERT INTO test2 (     class_id,                mark,                 std
                     ,      res_date,            exam_date)
            VALUES ( new_records(j).class_id, new_records (j).mark, new_records (j).std
                   , new_records(j).res_date, new_records (j).exam_date);

     delete from test1 select * from test2;
    END IF;
     END LOOP;
  new_records.DELETE;
  rows_inserted := 0;
  END AFTER STATEMENT;
END dup_ct;
/



Solution

This question is not yet answered, be the first one who answer using the comment. Later the confirmed answer will be published as the solution.

This Question and Answer are collected from stackoverflow and tested by JTuto community, is licensed under the terms of CC BY-SA 2.5. - CC BY-SA 3.0. - CC BY-SA 4.0.

people found this article helpful. What about you?