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;
/