Help Fixing ORA-01858: a non-numeric character was found where a numeric was expected.

Hi Experts,

My procedure compiles successfully. However upon execution, I am getting ORA-01858: a non-numeric character was found where a numeric was expected.

The error message does not tell which variable or column is causing the error. I am sure the experts will be able to step through the code and help me resolve the issue.

CREATE OR REPLACE PROCEDURE Sars_NRTV
 IS
  c_row_processed CONSTANT number(4) := 1000;
  v_my_loops  pls_integer;
  v_row_count pls_integer;
  v_Error_Code     Number;
  v_Srch_Trm       Varchar2(2000);
  v_Srch_Date      Date;
  v_Srch_Term_Id   Integer;
  v_Concept_Grp    Varchar2(500);
  v_Srch_Typ       Varchar2(128);
  v_Rsvld_Grp_Nm   Varchar2 (250); 
  v_Error_Message  Varchar2(100);
  Type sSrch_Trm IS TABLE OF     Varchar2 (2000);
  Type sDoc_Id IS TABLE OF       Number (12);
  Type sSrch_Term_Id is TABLE OF Integer;
  Type sSrch_Date IS TABLE OF    Date;
  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);
  Type sConcept_Grp IS TABLE OF  Varchar2 (500);
  Type sSrch_Typ IS TABLE OF     Varchar2(128);
  Type sRsvld_Grp_Nm IS TABLE OF Varchar2 (250); 
  l_sSrch_Trm              sSrch_Trm;
  l_sFilg_Dt               sFilg_Dt;
  l_sDOC_Id                sDoc_Id;
  l_sSrch_Date             sSrch_Date;
  l_sSrch_Term_Id          sSrch_Term_Id;
  l_sLST_UPD_TS            sLST_UPD_TS;
  l_sLGL_RAW_PRTY_FULL_NM  sLGL_RAW_PRTY_FULL_NM; 
  l_sConcept_Grp           sConcept_Grp;
  l_sSrch_Typ              sSrch_Typ;
  l_sRsvld_Grp_Nm          sRsvld_Grp_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 WI_SO_NO_REC   
        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 Srch_Trm, Srch_Date, Srch_Term_Id, Concept_Grp, Srch_typ, Rsvld_Grp_Nm 
       INTO v_Srch_Trm, v_Srch_Date, v_Srch_Term_Id, v_Concept_Grp, v_Srch_typ, v_Rsvld_Grp_Nm
       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.Srch_Trm, N.DOC_ID, D.FILG_DT, D.LST_UPD_TS,
          upper(F.LGL_RAW_PRTY_FULL_NM), c1_rec.Srch_Date, c1_rec.Srch_Term_Id, c1_rec.Concept_Grp, c1_rec.Srch_Typ, c1_rec.Rsvld_Grp_Nm    
          BULK COLLECT INTO  l_sSrch_Trm, l_sDoc_Id, l_sFilg_Dt, l_sLST_UPD_TS, l_sLGL_RAW_PRTY_FULL_NM, l_sSrch_Date, l_sSrch_Term_Id, 
          l_sConcept_Grp, l_sSrch_Typ, l_sRsvld_Grp_Nm         
          FROM BASE.NRTV N,  BASE.FILG_NSTUTN F, BASE.DOC D
          WHERE CONTAINS (N.NRTV_TXT, C1_REC.Srch_Trm)>0
          AND N.DOC_ID=F.DOC_ID
          AND N.DOC_ID=D.DOC_ID
          AND D.LST_UPD_TS > C1_REC.Srch_Date;         
          FORALL i IN 1..l_sSrch_Trm.count
          INSERT INTO WI_SO_NRTV_OUT VALUES (l_sSrch_Trm(i), l_sSrch_Date(i), l_sDoc_Id(i), l_sFilg_Dt(i), l_sLST_UPD_TS(i), l_sLGL_RAW_PRTY_FULL_NM(i),
          l_sSrch_Term_Id(i), l_sConcept_Grp(i), l_sSrch_Typ(i), l_sRsvld_Grp_Nm(i), 'DOC', 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 (Search_Term, code, message, info, Record_Source, Record_Time)
         Values  (c1_rec.SRCH_TRM,v_Error_Code, v_Error_Message, 'Oracle error occured', 'Sars_Of_Int_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_Int_NRTV',sysdate);
        Commit;
End Sars_NRTV;
/

Open in new window

Kamal AgnihotriAsked:
Who is Participating?
 
Kamal AgnihotriAuthor Commented:
Hi Sdstuber/Slightwv,

I found the error and fixed the procedure is producing the desired results. The problem was at this stage:

INSERT INTO WI_SO_NRTV_OUT VALUES (l_sSrch_Trm(i), l_sSrch_Date(i), l_sDoc_Id(i), l_sFilg_Dt(i), l_sLST_UPD_TS(i), l_sLGL_RAW_PRTY_FULL_NM(i),
          l_sSrch_Term_Id(i), l_sConcept_Grp(i), l_sSrch_Typ(i), l_sRsvld_Grp_Nm(i), 'DOC', sysdate);

The error   was due to the column miss-match with the date being inserted into. I broke it down to column-by-column  into INSERT INTO and then put the VALUES  matching the values for those colums. This is an extra step but makes the code crystal clear.

INSERT INTO WI_SO_NRTV_OUT (SRCH_TRM, SRCH_DATE, SRCH_TERM_ID, CONCEPT_GRP, SRCH_TYP, RSLVD_GRP_NM, DOC_ID, FILG_DT, LAST_UPDATE, LGL_RAW_PRTY_FULL_NM, TRIGGER_TXT, RUN_DATE)
          VALUES (l_sSrch_Trm(i), l_sSrch_Date(i), l_sSrch_Term_Id(i), l_sConcept_Grp(i), l_sSrch_Typ(i), l_sRslvd_Grp_Nm(i), l_sDoc_Id(i), l_sFilg_Dt(i), l_sLST_UPD_TS(i), l_sLGL_RAW_PRTY_FULL_NM(i), 'DOC', sysdate);

I want to thank Sdstuber and Slightwv for pointing me in the right direction.

I will close the question and assign points equally.
0
 
slightwv (䄆 Netminder) Commented:
>>I am sure the experts will be able to step through the code and help me resolve the issue.

Not without data.  That error comes from a problem with your data.

More often than not it comes from implicit string to date conversions.

Are any of the columns that you are loading into a date variable actually declared as varchar2 or char in the tables?
0
 
sdstuberCommented:
we can't determine which value is of the incorrect type without knowing the data types of your columns.

please post the ddl for the tables referenced in your procedure
0
 
Kamal AgnihotriAuthor Commented:
It is already mentioned above.
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.