4 Replies Latest reply: Aug 7, 2012 6:19 AM by 951418 RSS

    Continue inserting if any exception occurs

    951418
      Hi, need an help

      WHILE v_rec IS NOT NULL LOOP
      -- FOR rec_inv IN t_inv.first..t_inv.last LOOP
      DBMS_OUTPUT.PUT_LINE(t_inv(v_rec));
      v_inovice_id :=t_inv(v_rec);
      If t_inv(v_rec)=16249857 then
      null;
      end if;
      BEGIN
      insert_case(t_inv(v_rec),p_template_id,p_host_system,p_sabrix_env);
      Exception -- Added exception to handle error for Large Invoice ID which has more than 32760 characters
      when others then
      DBMS_OUTPUT.PUT_LINE('Error occured for Invoice Id: ' || t_inv(v_rec));
      End;
      v_rec := t_inv.NEXT(v_rec);
      v_t_cnt := v_t_cnt + 1;
      END LOOP;

      I have writen this block, this code will try to catch teh exception once it gets any large inpvice id with greter than 32760 characters and continue with Inserting other invoice ID which are stored in the t_inv pl/sql tables.

      But this is inserting duplicate REcords, i can see the t_inv.count is having the appropriate number of records like 50 but everytime when i run its inserted more than no of t_inv.count.

      Please suggest
        • 1. Re: Continue inserting if any exception occurs
          Karthick_Arp
          948415 wrote:
          Hi, need an help

          WHILE v_rec IS NOT NULL LOOP
          -- FOR rec_inv IN t_inv.first..t_inv.last LOOP
          DBMS_OUTPUT.PUT_LINE(t_inv(v_rec));
          v_inovice_id :=t_inv(v_rec);
          If t_inv(v_rec)=16249857 then
          null;
          end if;
          BEGIN
          insert_case(t_inv(v_rec),p_template_id,p_host_system,p_sabrix_env);
          Exception -- Added exception to handle error for Large Invoice ID which has more than 32760 characters
          when others then
          DBMS_OUTPUT.PUT_LINE('Error occured for Invoice Id: ' || t_inv(v_rec));
          End;
          v_rec := t_inv.NEXT(v_rec);
          v_t_cnt := v_t_cnt + 1;
          END LOOP;

          I have writen this block, this code will try to catch teh exception once it gets any large inpvice id with greter than 32760 characters and continue with Inserting other invoice ID which are stored in the t_inv pl/sql tables.

          But this is inserting duplicate REcords, i can see the t_inv.count is having the appropriate number of records like 50 but everytime when i run its inserted more than no of t_inv.count.

          Please suggest
          Drop your Looping. Do it in direct SQL. If not use BULK COLLECT. And to cache the error data use DML Error Logging

          [url http://docs.oracle.com/cd/E11882_01/server.112/e25494/tables004.htm#ADMIN10261]Avoiding Bulk INSERT Failures with DML Error Logging
          • 2. Re: Continue inserting if any exception occurs
            BluShadow
            when others then 
              DBMS_OUTPUT.PUT_LINE('Error occured for Invoice Id: ' || t_inv(v_rec));
            What awful code. How will anyone know what the error was that occurred?
            If you can't handle the error, done capture it, just let it raise.
            • 3. Re: Continue inserting if any exception occurs
              Hoek
              Here's a sort of summary regarding DML Error Logging, it gives you a quick overview:
              http://www.oracle-base.com/articles/10g/dml-error-logging-10gr2.php

              and here's a more detailed 'rantexplanation' regarding your WHEN OTHERS ;) :
              http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1509245700346768268#tom1535781400346575552
              • 4. Re: Continue inserting if any exception occurs
                951418
                the above code is correct.. i forgotremove the rollback statement in the insert_use_case proc, so everytime whenever i was inserting it was doign rollback and again inserting.