4 Replies Latest reply on Feb 24, 2013 2:00 AM by 940443

    Doubt on Bulk collect

    986110
      Hi,

      Ii have to insert lakhs of records from a source table to another table through bulk processing .If I am getting exception for some of the records then what I can do so that the insertion will not stop but the records for which I am getting the errors will be recorded somewhere and my execution will continue till the last record insertion.
      After the execution I can go and check for which records the insertion failed and why?

      Can anyone help me out in this?

      Your suggestion will behighly appreciated.

      Regards,
      SB2013
        • 1. Re: Doubt on Bulk collect
          sb92075
          SB2013 wrote:
          Hi,

          Ii have to insert lakhs of records from a source table to another table through bulk processing .If I am getting exception for some of the records then what I can do so that the insertion will not stop but the records for which I am getting the errors will be recorded somewhere and my execution will continue till the last record insertion.
          After the execution I can go and check for which records the insertion failed and why?

          Can anyone help me out in this?

          Your suggestion will behighly appreciated.

          Regards,
          SB2013
          when all else fails, Read The Fine Manual

          http://www.oracle.com/pls/db112/search?remark=quick_search&word=error+logging
          • 2. Re: Doubt on Bulk collect
            onedbguru
            you may be using the wrong tool to accomplish the task. I would look at DBMS_PARALLEL_EXECUTE (11gR2) to move these records. Depending on how many records per "chunK" it would process most and when you have errors, you can see which records had errors and process them separately and fix whatever issue caused the failure.

            If you still need to use bulk collect http://www.ltgtfy.com/?1=bulk+collect+error+processing and you should find the answer to your question.
            • 3. Re: Doubt on Bulk collect
              sgudipudi
              You could save the exceptions by using "SAVE EXCEPTIONS" clause in FOR ALL statement and using SQL%BULK_EXCEPTIONS cursor attributes.

              see the below link for more understanding the exceptions on bulk processing...


              http://www.oracle-base.com/articles/9i/bulk-binds-and-record-processing-9i.php#save_exceptions

              http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1422998100346727312


              hope this helps!
              • 4. Re: Doubt on Bulk collect
                940443
                Hi,

                The following code creates a collection with 100 rows, but sets the value of rows 50 and 51 to NULL. Since the above table does not allow nulls, these rows will result in an exception. The SAVE EXCEPTIONS clause allows the bulk operation to continue past any exceptions, but if any exceptions were raised in the whole operation, it will jump to the exception handler once the operation is complete. In this case, the exception handler just loops through the SQL%BULK_EXCEPTION cursor attribute to see what errors occured.

                SET SERVEROUTPUT ON
                DECLARE
                TYPE t_tab IS TABLE OF exception_test%ROWTYPE;

                l_tab t_tab := t_tab();
                l_error_count NUMBER;

                ex_dml_errors EXCEPTION;
                PRAGMA EXCEPTION_INIT(ex_dml_errors, -24381);
                BEGIN
                -- Fill the collection.
                FOR i IN 1 .. 100 LOOP
                l_tab.extend;
                l_tab(l_tab.last).id := i;
                END LOOP;

                -- Cause a failure.
                l_tab(50).id := NULL;
                l_tab(51).id := NULL;

                EXECUTE IMMEDIATE 'TRUNCATE TABLE exception_test';

                -- Perform a bulk operation.
                BEGIN
                FORALL i IN l_tab.first .. l_tab.last SAVE EXCEPTIONS
                INSERT INTO exception_test
                VALUES l_tab(i);
                EXCEPTION
                WHEN ex_dml_errors THEN
                l_error_count := SQL%BULK_EXCEPTIONS.count;
                DBMS_OUTPUT.put_line('Number of failures: ' || l_error_count);
                FOR i IN 1 .. l_error_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;
                END;
                /

                Number of failures: 2
                Error: 1 Array Index: 50 Message: ORA-01400: cannot insert NULL into ()
                Error: 2 Array Index: 51 Message: ORA-01400: cannot insert NULL into ()

                PL/SQL procedure successfully completed.