We help IT Professionals succeed at work.
Get Started

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

Kamal Agnihotri
on
146 Views
Last Modified: 2015-03-04
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;
/
Comment
Watch Question
Database Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012
Commented:
This problem has been solved!
Unlock 1 Answer and 5 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE