CODEWITHSUNDEEP
oracle-databaseplsqloracle11g
Em Ae
Em Ae

Reputation: 8724

Tracking failed transaction in bulk insert/update/delete

I have following code structure

PROCEDURE .. 
    PRAGMA EXCEPTION_INIT(dml_errors, -24381);
    l_errors            NUMBER;

BEGIN
  -- busines logic
  FORALL TABLE_1 do delete;
  FORALL TABLE_1 do update;
  FORALL TABLE_1 do insert;
  FORALL TABLE_2 do insert;
  FORALL TABLE_2 do update;
  FORALL TABLE_2 do insert;
EXCEPTION
  WHEN dml_errors then
    --extract error indexes
END;

Each of the forall loop deals with a separate table of array i.e., the loop which is deleting from table_1 would deal with a table of table_1_u index by pls_integer;

Each forall loop has save exceptions keyword

Now, how can i extract which "for" failed and "which record in that index failed".

Upvotes: 0

Views: 535

Answers (1)

Sebas
Sebas

Reputation: 21542

how can i extract which "for" failed

With the call stack (FORMAT_ERROR_BACKTRACE) Or by implementing some sort of steps:

PROCEDURE .. 
    PRAGMA EXCEPTION_INIT(dml_errors, -24381);
    l_errors            NUMBER;
    iStep NUMBER;
BEGIN
  -- busines logic
  iStep := 0;
  FORALL TABLE_1 do delete;
  iStep := 1;
  FORALL TABLE_1 do update;
  iStep := 2;
  FORALL TABLE_1 do insert;
  iStep := 3;
  FORALL TABLE_2 do insert;
  iStep := 4;
  FORALL TABLE_2 do update;
  iStep := 5;
  FORALL TABLE_2 do insert;
EXCEPTION
  WHEN dml_errors then
      IF iStep = 0 THEN
      ELSIF...
      END;
END;

which record in that index failed

By using the bulk_exceptions cursor:

PROCEDURE .. 
    PRAGMA EXCEPTION_INIT(dml_errors, -24381);
    l_errors            NUMBER;
    iStep NUMBER;
BEGIN
  -- busines logic
  iStep := 0;
  FORALL TABLE_1 do delete;
  iStep := 1;
  FORALL TABLE_1 do update;
  iStep := 2;
  FORALL TABLE_1 do insert;
  iStep := 3;
  FORALL TABLE_2 do insert;
  iStep := 4;
  FORALL TABLE_2 do update;
  iStep := 5;
  FORALL TABLE_2 do insert;
EXCEPTION
  WHEN dml_errors then

      FOR i IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP
         DBMS_OUTPUT.PUT_LINE('Error #' || i || ' occurred during '|| 
            'iteration #' || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX); 
             DBMS_OUTPUT.PUT_LINE('Error message is ' ||
             SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
      END LOOP;

      IF iStep = 0 THEN
      ELSIF...
      END;
END;

Upvotes: 3

Related Questions