This discussion is archived
4 Replies Latest reply: Aug 7, 2012 4:19 AM by 951418 RSS

Continue inserting if any exception occurs

951418 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Guru Moderator
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points