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?

[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.

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:
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

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:
It is already mentioned above.
0
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.