-1

FORALL update is updating only last record from collection and it's giving error ORA-22160: element at index [1] does not exist. I have scenario were i have to update million of records. When i execute the below code the update is happening only for the 100th record remaining 99 records remains same( i tested with 100 records). after execution it's giving ORA-22160: element at index [1] does not exist. i am using oracle 9i database.need suggestion for this issue.

Declare 
 CURSOR tk_iflow_cur
  IS
    SELECT DISTINCT top.rule_id,
      top.rule_item_id,
      msib.segment2
    FROM iflow_rules top,
      iflow_active_rules msib
    WHERE top.rule_id = msib.rule_item_id
    AND msib.organization_id  = 5039 and rownum<=100
    ORDER BY top.ora_inv_item_id; -- cursor fetches 100 row 
  ---Variable declaration
TYPE l_iflow_id
IS
  TABLE OF VARCHAR2(200) INDEX BY BINARY_INTEGER;
TYPE l_iflow_org
IS
  TABLE OF NUMBER INDEX BY BINARY_INTEGER;
TYPE l_iflow_inv_id
IS
  TABLE OF NUMBER INDEX BY BINARY_INTEGER;
TYPE l_r12_inv_id
IS
  TABLE OF NUMBER;
TYPE l_r12_item_key
IS
  TABLE OF VARCHAR2(200) INDEX BY BINARY_INTEGER;
TYPE l_r12_iflow
IS
  TABLE OF VARCHAR2(200) INDEX BY BINARY_INTEGER;
TYPE l_omar_item_id
IS
  TABLE OF NUMBER INDEX BY BINARY_INTEGER;
TYPE l_omar_seg2
IS
  TABLE OF VARCHAR2(200) INDEX BY BINARY_INTEGER;
  tk_iflow_id l_iflow_id;
  tk_iflow_org l_iflow_org;
  tk_iflow_inv_id l_iflow_inv_id;
  t_omar_item_id l_omar_item_id;
  t_omar_seg2 l_omar_seg2;
  r12_inv_id l_r12_inv_id:=l_r12_inv_id();
  r12_item_key l_r12_item_key;
  r12_iflow l_r12_iflow;
  user_excep  EXCEPTION;
  v_err_count NUMBER;
  PRAGMA EXCEPTION_INIT (user_excep, -24381);
  r12_item_id2 VARCHAR2(200);


BEGIN --
  tk_iflow_id.DELETE;
  tk_iflow_inv_id.DELETE;
  OPEN tk_iflow_cur;
  LOOP --Cursor Loop
    tk_iflow_id.DELETE;
    tk_iflow_inv_id.DELETE;
    t_omar_seg2.DELETE;
    FETCH tk_iflow_cur BULK COLLECT INTO tk_iflow_id, tk_iflow_inv_id, t_omar_seg2; ---100 records assigned to variables
    FOR i IN 1..tk_iflow_id.COUNT -- to store the cursor value
    LOOP                         --for Loop
      BEGIN
        --Comment : passing the cursor value to derive new records from different instance for updating the records
        SELECT DISTINCT segment1,
          inventory_item_id,
          item_type
        INTO r12_iflow(i),
          r12_item_id2,
          r12_item_key(i)
        FROM iflow.ifl_active_rules@R12_db_link
        WHERE segment1           =tk_iflow_id(i)
        AND NVL(SEGMENT2,'NULL') = NVL(t_omar_seg2(i),'NULL')
        AND organization_id      =5063;
      EXCEPTION
      WHEN OTHERS THEN
        r12_inv_id(i)   := 0;
        r12_item_key(i) := 0;
      END;
      r12_inv_id :=l_r12_inv_id();   
      r12_inv_id.EXTEND (i);
      r12_inv_id (i) := r12_item_id2;

    END LOOP; --end for loop
    BEGIN
      FORALL i IN r12_inv_id.FIRST..r12_inv_id.LAST SAVE EXCEPTIONS -- for all the derived records i'm updating the target table.100 records i'm  updating 
      UPDATE iflow_active_rules 
      SET ora_org_id      =5063,
        ora_inv_item_id   =r12_inv_id(i)
      WHERE iflow_id       =r12_iflow(i)
      AND ora_inv_item_id = tk_iflow_inv_id(i);
    EXCEPTION
    WHEN user_excep THEN
      v_err_count := SQL%BULK_EXCEPTIONS.COUNT;
      FOR i       IN 1 .. v_err_count
      LOOP
        DBMS_OUTPUT.put_line ( 'Error: ' || i || ' Array Index: ' || SQL%BULK_EXCEPTIONS (i).ERROR_INDEX || ' Message: ' || SQLERRM (SQL%BULK_EXCEPTIONS (i).ERROR_CODE) );
      END LOOP;
    END;
    BEGIN
      FORALL i IN r12_inv_id.FIRST .. r12_inv_id.LAST SAVE EXCEPTIONS
      UPDATE iflow_rules --- updating openup system iflow cost details
      SET ora_org_id      =5063,
        ora_inv_item_id   =r12_inv_id(i)
      WHERE iflow_id       =r12_iflow(i)
      AND ora_inv_item_id = tk_iflow_inv_id(i) ;
    EXCEPTION
    WHEN user_excep THEN
      v_err_count := SQL%BULK_EXCEPTIONS.COUNT;
      FOR i       IN 1 .. v_err_count
      LOOP
        DBMS_OUTPUT.put_line ( 'Error: ' || i || ' Array Index: ' || SQL%BULK_EXCEPTIONS (i).ERROR_INDEX || ' Message: ' || SQLERRM (SQL%BULK_EXCEPTIONS (i).ERROR_CODE) );
      END LOOP;
    END;
    EXIT
  WHEN tk_iflow_id.COUNT=0;
    COMMIT;
  END LOOP; --End Cursor Loop
  CLOSE tk_iflow_cur;
END;
Kevin
  • 1
  • 1
  • 3

1 Answers1

0

This is happening because you are using r12_iflow collection in the scope of collection r12_inv_id where it not not accessible. FORALL statement is differnt the FOR LOOP. Even though they work pretty on same logic but they differs on scope of usage of variables. You can create a RECORD in your case and do the processing. See below how you can do it. PS Not tested. 

DECLARE
   CURSOR tk_iflow_cur
   IS
        SELECT DISTINCT top.rule_id, top.rule_item_id, msib.segment2
          FROM iflow_rules top, iflow_active_rules msib
         WHERE     top.rule_id = msib.rule_item_id
               AND msib.organization_id = 5039
               AND ROWNUM <= 100
      ORDER BY top.ora_inv_item_id;                  -- cursor fetches 100 row


     Type var_cur is table of tk_iflow_cur%rowtype index by pls_integer;      
     l_cur   var_cur; 

     --Record of your variables.
     TYPE var_rec is RECORD 
     (
        l_r12_iflow  VARCHAR2 (200),
        l_r12_inv_id number,
        l_r12_item_key VARCHAR2 (200)

     );

     TYPE rec is table of var_rec index by pls_integer;

     --Variable declared to hold the result of your cursor.
     l_rec   rec;

   user_excep        EXCEPTION;
   v_err_count       NUMBER;
   PRAGMA EXCEPTION_INIT (user_excep, -24381);
   r12_item_id2      VARCHAR2 (200);
BEGIN                                                                           
   OPEN tk_iflow_cur;

   LOOP                                                          --Cursor Loop     

      FETCH tk_iflow_cur  BULK COLLECT INTO l_cur LIMIT 100; ---100 records assigned to variables        

      FOR i IN 1 .. l_cur.COUNT             -- to store the cursor value
      LOOP                                                          --for Loop
         BEGIN
            --Comment : passing the cursor value to derive new records from different instance for updating the records
            SELECT DISTINCT segment1, inventory_item_id, item_type
              INTO l_rec(i).l_r12_iflow ,l_rec(i).l_r12_inv_id ,l_rec(i).l_r12_item_key
              FROM iflow.ifl_active_rules@R12_db_link
             WHERE  segment1 = l_cur(i).rule_id
              AND NVL (SEGMENT2, 'NULL') = NVL (l_cur(i).segment2 , 'NULL')
              AND organization_id = 5063;
         EXCEPTION
            WHEN OTHERS
            THEN
               l_rec(i).l_r12_iflow := 0; 
               l_rec(i).l_r12_inv_id:= 0; 
               l_rec(i).l_r12_item_key := '';
         END;

      END LOOP;                                                 --end for loop

      BEGIN
         FORALL i IN 1 .. l_rec.COUNT SAVE EXCEPTIONS -- for all the derived records i'm updating the target table.100 records i'm  updating
            UPDATE iflow_active_rules
               SET ora_org_id = 5063, 
                   ora_inv_item_id = l_rec(i).l_r12_inv_id
             WHERE     iflow_id = l_rec(i).l_r12_iflow 
              AND ora_inv_item_id = l_rec(i).l_r12_inv_id;

      EXCEPTION
         WHEN user_excep
         THEN
            v_err_count := SQL%BULK_EXCEPTIONS.COUNT;

            FOR i IN 1 .. v_err_count
            LOOP
               DBMS_OUTPUT.put_line (
                     'Error: '
                  || i
                  || ' Array Index: '
                  || SQL%BULK_EXCEPTIONS (i).ERROR_INDEX
                  || ' Message: '
                  || SQLERRM (SQL%BULK_EXCEPTIONS (i).ERROR_CODE));
            END LOOP;
      END;

      BEGIN
         FORALL i IN 1..l_rec.COUNT SAVE EXCEPTIONS
            UPDATE iflow_rules   --- updating openup system iflow cost details
               SET ora_org_id = 5063, 
                   ora_inv_item_id = l_rec(i).l_r12_inv_id
             WHERE     iflow_id = l_rec(i).l_r12_iflow 
             AND ora_inv_item_id = l_rec(i).l_r12_inv_id;

      EXCEPTION
         WHEN user_excep
         THEN
            v_err_count := SQL%BULK_EXCEPTIONS.COUNT;

            FOR i IN 1 .. v_err_count
            LOOP
               DBMS_OUTPUT.put_line (
                     'Error: '
                  || i
                  || ' Array Index: '
                  || SQL%BULK_EXCEPTIONS (i).ERROR_INDEX
                  || ' Message: '
                  || SQLERRM (SQL%BULK_EXCEPTIONS (i).ERROR_CODE));
            END LOOP;
      END;

      EXIT WHEN tk_iflow_cur%NOTFOUND;
    END LOOP;                                                 --End Cursor Loop

   COMMIT;
   CLOSE tk_iflow_cur;

END;
XING
  • 9,608
  • 4
  • 22
  • 38