1 2 Previous Next 20 Replies Latest reply on Mar 23, 2010 9:09 AM by Asheesh Ambardar Go to original post
      • 15. Re: Bulk collect Error
        Asheesh Ambardar
        The exception is coming because of wrong reference to e_bulk exception.
        If you change the exception handling to catch OTHERS, then it should work fine.
        SQL> DECLARE
          2     TYPE t_emp_type IS TABLE OF emp%ROWTYPE
          3        INDEX BY BINARY_INTEGER;
          4
          5     t_emp_tab   t_emp_type;
          6     e_bulk      EXCEPTION;
          7     PRAGMA EXCEPTION_INIT (e_bulk, -20000);
          8  BEGIN
          9     SELECT   *
         10     BULK COLLECT INTO t_emp_tab
         11         FROM emp
         12     ORDER BY empno;
         13
         14     FORALL i IN t_emp_tab.FIRST .. t_emp_tab.LAST SAVE EXCEPTIONS
         15        INSERT INTO emp_bulk
         16             VALUES t_emp_tab (i);
         17  EXCEPTION
         18     WHEN OTHERS
         19     THEN
         20        FOR j IN 1 .. SQL%BULK_EXCEPTIONS.COUNT
         21        LOOP
         22           DBMS_OUTPUT.put_line
         23                     (   t_emp_tab (SQL%BULK_EXCEPTIONS (j).ERROR_INDEX).empno
         24                      || ' , '
         25                      || t_emp_tab (SQL%BULK_EXCEPTIONS (j).ERROR_INDEX).ename
         26                      || ' , (sal ='
         27                      || t_emp_tab (SQL%BULK_EXCEPTIONS (j).ERROR_INDEX).sal
         28                      || ' ) :'
         29                      || SQLERRM (-SQL%BULK_EXCEPTIONS (j).ERROR_CODE)
         30                     );
         31        END LOOP;
         32  END;
         33  /
        43 , AA , (sal =43 ) :ORA-02290: check constraint (.) violated
        7369 , SMITH , (sal =800 ) :ORA-02290: check constraint (.) violated
        7900 , JAMES , (sal =950 ) :ORA-02290: check constraint (.) violated
        
        PL/SQL procedure successfully completed.
        OR* , If you know the correct error code(which is 24381 in this scenario) you should alter PRAGMA to assign the correct ORA exception.
          7     PRAGMA EXCEPTION_INIT (e_bulk, -24381);
        Cheers,
        AA
        • 16. Re: Bulk collect Error
          Prazy
          When you catch a specific exception., you have to supply the correct error code. here replace -20000 to -24381 and run it

          i.e. change Pragma Exception_Init(e_bulk, -20000); to Pragma Exception_Init(e_bulk, -24381);
          PRAZY@11gR1> create table emp_bulk as select * from emp where 1=2;
          
          Table created.
          
          Elapsed: 00:00:00.06
          PRAZY@11gR1> alter table emp_bulk add constraint emp_chk check (sal > 1000);
          
          Table altered.
          
          Elapsed: 00:00:00.12
          PRAZY@11gR1> ed
          Wrote file afiedt.buf
          
          Declare
          Type t_emp_type Is Table Of emp%Rowtype Index By Binary_Integer;
          t_emp_tab t_emp_type;
          e_bulk Exception;
          Pragma Exception_Init(e_bulk, -24381);
          Begin
          Select * Bulk Collect Into t_emp_tab From emp Order By empno;
          Forall i In t_emp_tab.First..t_emp_tab.Last save Exceptions
          Insert Into emp_bulk Values t_emp_tab(i);
          Exception
          When e_bulk Then
          For j In 1..Sql%bulk_exceptions.Count
          Loop
          Dbms_Output.put_Line (t_emp_tab(Sql%bulk_exceptions(j).error_index).empno
          || ' , ' ||t_emp_tab(Sql%bulk_exceptions(j).error_index).ename
          ||' , (sal ='||t_emp_tab(Sql%bulk_exceptions(j).error_index).Sal
          || ' ) :' ||Sqlerrm (-Sql%bulk_exceptions(j).error_code)
          );
          End Loop;
          End;
          7369 , SMITH , (sal =800 ) :ORA-02290: check constraint (.) violated
          7900 , JAMES , (sal =950 ) :ORA-02290: check constraint (.) violated
          
          PL/SQL procedure successfully completed.
          
          Elapsed: 00:00:00.03
          Regards,
          Prazy
          • 17. Re: Bulk collect Error
            Apple
            How can u tell this is correct error code (-24381)
            • 18. Re: Bulk collect Error
              Apple
              how could u take these error code (-24381) for this suitable in this scenario
              • 19. Re: Bulk collect Error
                Prazy
                Thats the error you got ;)
                ERROR:
                ORA-24381: error(s) in array DML
                ORA-06512: at line 8
                And, close the thread by marking answered if you think your issue got resolved.

                Regards,
                Prazy
                1 person found this helpful
                • 20. Re: Bulk collect Error
                  Asheesh Ambardar
                  Hi Apple,

                  I kinda think what is bothering you is(correct me if i am wrong) "+If the only app exception that i am expecting is 'check constraint violated' and the ORA code for that exception is -02290, then why are we catching -24381+".

                  The answer to it is that:
                  While using bulk DML(i.e. FORALL), the parent-most exception handler does not show the actual error/exception. Instead it will always show a generic ORA-24381: error(s) in array DML error.

                  Only when you drill down to SQL%BULK_EXCEPTIONS level, you will be able to find the actual failure reason.
                  As a general practice this is how you should frame your bulk DML error handler:
                  Explicit handling for ORA-24381 should not be there. Only error codes that are required to be handled(e.g. Check constraint violation etc) should be handled.
                  DECLARE
                     TYPE t_emp_type IS TABLE OF emp%ROWTYPE
                        INDEX BY BINARY_INTEGER;
                  
                     t_emp_tab                        t_emp_type;
                     e_bulk                           EXCEPTION;
                     -- ORA code for check constraint violation is 2290
                     ln_chk_cons_violation   CONSTANT NUMBER         := 2290;
                     ln_iteration                     NUMBER;
                     ln_error                         NUMBER;
                     lv_message                       VARCHAR2 (512);
                  BEGIN
                     SELECT   *
                     BULK COLLECT INTO t_emp_tab
                         FROM emp
                     ORDER BY empno;
                  
                     FORALL i IN t_emp_tab.FIRST .. t_emp_tab.LAST SAVE EXCEPTIONS
                        INSERT INTO emp_bulk
                             VALUES t_emp_tab (i);
                  EXCEPTION
                     -- Do not have any handling for 24381, instead catch it as a generic exception
                     WHEN OTHERS
                     THEN
                        FOR i IN 1 .. SQL%BULK_EXCEPTIONS.COUNT
                        LOOP
                           ln_iteration := SQL%BULK_EXCEPTIONS (i).ERROR_INDEX;
                           ln_error := SQL%BULK_EXCEPTIONS (i).ERROR_CODE;
                           lv_message := SQLERRM (-ln_error);
                  
                           -- Do the exception specific handling here
                           IF (ln_error = ln_chk_cons_violation)
                           THEN
                              -- Add handler/dbms_output here
                              DBMS_OUTPUT.put_line (   'Exception occurred at iteration='
                                                    || ln_iteration
                                                    || ' ErrorCode='
                                                    || ln_error
                                                    || ' ErrorMessage='
                                                    || lv_message
                                                   );
                           END IF;
                        END LOOP;
                  END;
                  /
                  OUTPUT after execution:
                  Exception occurred at iteration=1 ErrorCode=2290 ErrorMessage=ORA-02290: check constraint (.) violated
                  Exception occurred at iteration=2 ErrorCode=2290 ErrorMessage=ORA-02290: check constraint (.) violated
                  Exception occurred at iteration=13 ErrorCode=2290 ErrorMessage=ORA-02290: check constraint (.) violated
                  Hope it helps!

                  Cheers,
                  AA
                  1 2 Previous Next