Trapping rows throwing errors in Errors table

My procedure is working fine. Thanks to Sdstuber and Slightwv.

I want to take it a step further. I want to trap the rows that had errors in the errors table. Right now my procedure as listed below is working like this:

1) Source Table (WI_SO_NRTV) has records to be processed.
2) The procedure when executed, parses the Source Table, if the "Search String"  is correct, finds associated records from three other tables and  populates the output table (WI_SO_NRTV_OUT)
3) If the "Search String" has "bad data" or unproperly formated data, the records are inserted into  error table WI_SO_ERRM

I would like the error table WI_SO_ERRM to also list in an additional column, the corresponding bad data "Search String" that caused the error.

-- Working procedure.

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 WI_SO_NRTV to find matching records in Tables
    -- BASE.NRTV, BASE.DOC And populate table WI_SO_NRTV_OUT
      LOOP
          BEGIN          
       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;
     
      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;
    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;
End Sars_Of_Interest_NRTV;
/
Kamal AgnihotriAsked:
Who is Participating?
 
slightwv (䄆 Netminder) Commented:
Best case just add c1_rec.NAME_IN  to the insert in the exception handler.

Worst case:
declare a variable and set it above the select:
current_search_term := c1_rec.NAME_IN;
Select ...

Then use that variable in the exception handler.
0
 
Kamal AgnihotriAuthor Commented:
This worked: Best case just add c1_rec.NAME_IN  to the insert in the exception handler

Thanks a lot.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.