Passing the control back to the procedure after error is encountered.

My procedure workes fine as long as there is no data error. Suppose there are 10 rows of records to be processed, and line 6 has data error.  When the procedure is executed, as soon as the procedure hits line 6, "when Others then"  kiks in and the procedure terminates.    

The question is how to pass the control back to the procedure AFTER it hits line 6 so that following lines (7 to 10) can be processed.

Extension to the question would be, say lines 3, 7 and 9 have bad data. the procedure shold process all 10 rows, sending the output from lines 1,2, 5,6, 8 and 10 and the error handler recocring data errors for lines 3, 7 , 9.


CREATE OR REPLACE PROCEDURE Sars_Of_Interest_NRTV
 IS
  c_row_processed CONSTANT number(4) := 1000;
  v_my_loops  pls_integer;
  v_row_count pls_integer;
  v_Error_Code  Number;
  v_Name_In   Varchar2(2000);
  v_Search_Date Date;
  v_Error_Message Varchar2(100);
  Type sSearch_Term IS TABLE OF  Varchar2 (2000);
  Type sDoc_Id IS TABLE OF  Number (12);
  Type sFilg_Dt IS Table OF Date;
  Type sLST_UPD_TS IS Table OF Date;
  Type sLGL_RAW_PRTY_FULL_NM is TABLE OF Varchar2 (1000);
  l_sSearch_Term sSearch_Term;
  l_sFilg_Dt sFilg_Dt;
  l_sDOC_Id   sDoc_Id;
  l_sLST_UPD_TS   sLST_UPD_TS;
  l_sLGL_RAW_PRTY_FULL_NM   sLGL_RAW_PRTY_FULL_NM;
  e_no_records_found   Exception;      
     
   Begin
   -- Empty the Destination Table
     Execute Immediate ('Truncate Table WI_SO_NRTV_OUT');
     v_my_loops := 0;
   -- If the source table (WI_SO_NRTV) has no records, Raise exeception,
   -- Enter "No Record Found" in Temp_ALL_Sars_Of_Interest  
        Select count (*) into v_row_count FROM WI_SO_NRTV ;
        If     v_row_count = 0 Then
           RAISE e_no_records_found;
   -- Create a cursor to hold values from table WI_SO_NRTV          
    Else
       DECLARE
         CURSOR c1_cur
    IS
       SELECT NAME_IN, Search_Date
       INTO v_Name_In, v_Search_Date
       FROM WI_SO_NRTV ;
     BEGIN
        For c1_rec IN c1_cur
    -- Loop through table WI_SO_NRTV to find matching records in Tables
    -- BASE.NRTV, BASE.DOC And populate table WI_SO_NRTV_OUT
      LOOP
       SELECT
          c1_rec.NAME_IN SEARCH_TERM, N.DOC_ID DOC_ID, D.FILG_DT Filing_date, D.LST_UPD_TS LAST_UPDATE,
          upper(F.LGL_RAW_PRTY_FULL_NM) LGL_RAW_PRTY_FULL_NM    
          BULK COLLECT INTO  l_sSearch_Term,  l_sDoc_Id, l_sFilg_Dt, l_sLST_UPD_TS, l_sLGL_RAW_PRTY_FULL_NM
          FROM BASE.NRTV N,  BASE.FILG_NSTUTN F, BASE.DOC D
          WHERE CONTAINS (N.NRTV_TXT, C1_REC.NAME_IN)>0
          AND N.DOC_ID=F.DOC_ID
          AND N.DOC_ID=D.DOC_ID
          AND D.LST_UPD_TS > C1_REC.Search_Date;        
          FORALL i IN 1..l_sSearch_Term.count
          INSERT INTO WI_SO_NRTV_OUT VALUES (l_sSearch_Term(i), l_sDoc_Id(i), l_sFilg_Dt(i), l_sLST_UPD_TS(i), l_sLGL_RAW_PRTY_FULL_NM(i), sysdate);      
      v_my_loops := v_my_loops +1;
      If mod(c1_cur%rowcount, c_row_processed) =0 Then
         NULL;
      End If;
    End Loop;
    Commit;
   End;
  End If;
EXCEPTION
    When e_no_records_found Then
        Insert Into WI_SO_NO_REC (out_Put_SARS, Record_Source, Record_Time) VALUES ('Input_Table has no records to process', 'Sars_Of_Interest_NRTV',sysdate);
        Commit;
    When Others then
       v_Error_Code := SQLCODE;
       v_Error_Message := Substr (SQLERRM, 1, 75);
       Insert Into WI_SO_ERRM (code, message, info, Record_Source, Record_Time)
         Values (v_Error_Code, v_Error_Message, 'Oracle error occured', 'Sars_Of_Interest_NRTV', sysdate);        
Commit;
End Sars_Of_Interest_NRTV;
/
Kamal AgnihotriAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
put a begin/exception/end block inside the loop and capture the errors there


For c1_rec IN c1_cur
loop
BEGIN
      <your code here that may or may not fail>
EXCEPTION
   WHEN OTHERS THEN
  v_Error_Code := SQLCODE;
       v_Error_Message := Substr (SQLERRM, 1, 75);
       Insert Into WI_SO_ERRM (code, message, info, Record_Source, Record_Time)
         Values (v_Error_Code, v_Error_Message, 'Oracle error occured', 'Sars_Of_Interest_NRTV', sysdate);        
Commit;
end;

END LOOP;
Mark GeerlingsDatabase AdministratorCommented:
Sdstuber is correct, you need a local exception-handler (which requires a BEGIN... EXCEPTION ... END; block) inside your loop if you want your loop to continue past an error.
Kamal AgnihotriAuthor Commented:
Hi experts,

I madethe modification suggested. But the procedure is erroring out on line starting : When Others then

error syntax check: Found 'When': Expecting:; -or, := -or...........

Else
       DECLARE
         CURSOR c1_cur
    IS
       SELECT NAME_IN, Search_Date
       INTO v_Name_In, v_Search_Date
       FROM WI_SO_NRTV ;
     BEGIN
        For c1_rec IN c1_cur
    -- Loop through WI_SO_NRTV to find matching records in Tables
    -- AABASE.NRTV, AABASE.DOC And populate table WI_SO_NRTV_OUT
      LOOP
        BEGIN
          EXCEPTION
             When Others then
       v_Error_Code := SQLCODE;
       v_Error_Message := Substr (SQLERRM, 1, 75);
       Insert Into WI_SO_ERRM (code, message, info, Record_Source, Record_Time)
         Values (v_Error_Code, v_Error_Message, 'Oracle error occured', 'Sars_Of_Interest_NRTV', sysdate);        
           Commit;
        End;              
       SELECT
          c1_rec.NAME_IN SEARCH_TERM, N.DOC_ID DOC_ID, D.FILG_DT Filing_date, D.LST_UPD_TS LAST_UPDATE,
          upper(F.LGL_RAW_PRTY_FULL_NM) LGL_RAW_PRTY_FULL_NM    
          BULK COLLECT INTO  l_sSearch_Term,  l_sDoc_Id, l_sFilg_Dt, l_sLST_UPD_TS, l_sLGL_RAW_PRTY_FULL_NM
          FROM AABASE.NRTV N,  AABASE.FILG_NSTUTN F, AABASE.DOC D
          WHERE CONTAINS (N.NRTV_TXT, C1_REC.NAME_IN)>0
          AND N.DOC_ID=F.DOC_ID
          AND N.DOC_ID=D.DOC_ID
          AND D.LST_UPD_TS > C1_REC.Search_Date;        
          FORALL i IN 1..l_sSearch_Term.count
          INSERT INTO WI_SO_NRTV_OUT VALUES (l_sSearch_Term(i), l_sDoc_Id(i), l_sFilg_Dt(i), l_sLST_UPD_TS(i), l_sLGL_RAW_PRTY_FULL_NM(i), sysdate);      
      v_my_loops := v_my_loops +1;
      If mod(c1_cur%rowcount, c_row_processed) =0 Then
         NULL;
      End If;
    End Loop;
    Commit;
   End;
  End If;
EXCEPTION
    When e_no_records_found Then
        Insert Into WI_SO_NO_REC (out_Put_SARS, Record_Source, Record_Time) VALUES ('Input_Table has no records to process', 'Sars_Of_Interest_NRTV',sysdate);
        Commit;
Sars_Of_Interest_NRTV_Mod_01;
/
--############################################################


The entire procedure is like this:

CREATE OR REPLACE PROCEDURE Sars_Of_Interest_NRTV_Mod_01
 IS
  c_row_processed CONSTANT number(4) := 1000;
  v_my_loops  pls_integer;
  v_row_count pls_integer;
  v_Error_Code  Number;
  v_Name_In   Varchar2(2000);
  v_Search_Date Date;
  v_Error_Message Varchar2(100);
  Type sSearch_Term IS TABLE OF  Varchar2 (2000);
  Type sDoc_Id IS TABLE OF  Number (12);
  Type sFilg_Dt IS Table OF Date;
  Type sLST_UPD_TS IS Table OF Date;
  Type sLGL_RAW_PRTY_FULL_NM is TABLE OF Varchar2 (1000);
  l_sSearch_Term sSearch_Term;
  l_sFilg_Dt sFilg_Dt;
  l_sDOC_Id   sDoc_Id;
  l_sLST_UPD_TS   sLST_UPD_TS;
  l_sLGL_RAW_PRTY_FULL_NM   sLGL_RAW_PRTY_FULL_NM;
  e_no_records_found   Exception;      
     
   Begin
   -- Empty the Destination Table
     Execute Immediate ('Truncate Table WI_SO_NRTV_OUT');
     v_my_loops := 0;
   -- If the source table (WI_SO_NRTV) has no records, Raise exeception,
   -- Enter "No Record Found" in Temp_ALL_Sars_Of_Interest  
        Select count (*) into v_row_count FROM WI_SO_NRTV ;
        If     v_row_count = 0 Then
           RAISE e_no_records_found;
   -- Create a cursor to hold values from table WI_SO_NRTV          
    Else
       DECLARE
         CURSOR c1_cur
    IS
       SELECT NAME_IN, Search_Date
       INTO v_Name_In, v_Search_Date
       FROM WI_SO_NRTV ;
     BEGIN
        For c1_rec IN c1_cur
    -- Loop through QUERY_ORIGIN to find matching records in Tables
    -- AABASE.NRTV, AABASE.DOC And populate table OUT_Near_Sars_Of_Interest
      LOOP
        BEGIN
          EXCEPTION
             When Others then
       v_Error_Code := SQLCODE;
       v_Error_Message := Substr (SQLERRM, 1, 75);
       Insert Into WI_SO_ERRM (code, message, info, Record_Source, Record_Time)
         Values (v_Error_Code, v_Error_Message, 'Oracle error occured', 'Sars_Of_Interest_NRTV', sysdate);        
           Commit;
        End;              
       SELECT
          c1_rec.NAME_IN SEARCH_TERM, N.DOC_ID DOC_ID, D.FILG_DT Filing_date, D.LST_UPD_TS LAST_UPDATE,
          upper(F.LGL_RAW_PRTY_FULL_NM) LGL_RAW_PRTY_FULL_NM    
          BULK COLLECT INTO  l_sSearch_Term,  l_sDoc_Id, l_sFilg_Dt, l_sLST_UPD_TS, l_sLGL_RAW_PRTY_FULL_NM
          FROM AABASE.NRTV N,  AABASE.FILG_NSTUTN F, AABASE.DOC D
          WHERE CONTAINS (N.NRTV_TXT, C1_REC.NAME_IN)>0
          AND N.DOC_ID=F.DOC_ID
          AND N.DOC_ID=D.DOC_ID
          AND D.LST_UPD_TS > C1_REC.Search_Date;        
          FORALL i IN 1..l_sSearch_Term.count
          INSERT INTO WI_SO_NRTV_OUT VALUES (l_sSearch_Term(i), l_sDoc_Id(i), l_sFilg_Dt(i), l_sLST_UPD_TS(i), l_sLGL_RAW_PRTY_FULL_NM(i), sysdate);      
      v_my_loops := v_my_loops +1;
      If mod(c1_cur%rowcount, c_row_processed) =0 Then
         NULL;
      End If;
    End Loop;
    Commit;
   End;
  End If;
EXCEPTION
    When e_no_records_found Then
        Insert Into WI_SO_NO_REC (out_Put_SARS, Record_Source, Record_Time) VALUES ('Input_Table has no records to process', 'Sars_Of_Interest_NRTV',sysdate);
        Commit;
Sars_Of_Interest_NRTV_Mod_01;
/

Please help.

Thanks a lot.
sdstuberCommented:
 BEGIN
          EXCEPTION
             When Others then

Open in new window



that doesn't make sense.

the order should be..

Loop

begin
        <<<< your code>>>>
exception 
   when others then   <<< your exception handler >>>
end;

end loop;

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Kamal AgnihotriAuthor Commented:
Hi Sdstuber,

That worked. Thanks a lot.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.