ORA-02055 ORA-20014 ORA-06502

declare
  lncount number;
begin
  -- Call the procedure
  sp_impcodweb(oresult         => :oresult,
               imuact          => :imuact,
               infirmnumber    => :infirmnumber,
               icfilename      => :icfilename,
               icfiledirectory => :icfiledirectory,
               icsrcfile       => :icsrcfile,
               icfiletime      => :icfiletime,
               icoutfile       => :icoutfile);
  dbms_output.put_line(:oresult);
  Select Count(*)
    into lncount
    From Tbltempcoddetails
   Where Nfirmnumber = :infirmnumber
     And Ninstructiontype = 904
     And Ntransactiontype = -99;
  dbms_output.put_line(lncount);

  Merge Into Tbl904offmarket T1
  Using (Select  Nfirmnumber,
                Cclientboid,
                Dtransactiondate,
                Nnsdldpmtransactionno,
                Cisincode,
                Ctranstatus
           From Tbltempcoddetails
          Where Nfirmnumber = :infirmnumber
            And Ninstructiontype = 904
            And Ntransactiontype = -99) t
  On (T1.Nfirmnumber = t.Nfirmnumber And T1.Cclientboid = t.Cclientboid And T1.Dexecutiondate = t.Dtransactiondate
  And T1.Cisincode = t.Cisincode  And To_Number(Trim(T1.Cinstructionreferenceno)) = t.Nnsdldpmtransactionno)
  When Matched Then
    Update Set T1.Ctranstatus = t.Ctranstatus;
  Dbms_Output.Put_Line(To_Char(Sql%Rowcount) || ' rows merged. Sudi');
end;
/

Error invalid number at line -- Merge Into Tbl904offmarket T1


1) solution

select t1.Cinstructionreferenceno from Tbl904offmarket t1  WHERE REGEXP_LIKE(t1.Cinstructionreferenceno, '[[:alpha:]]')

select to_number(t1.Cinstructionreferenceno0 from Tbl904offmarket t1  WHERE REGEXP_LIKE(t1.Cinstructionreferenceno, '[[:alpha:]]')

2)

CREATE OR REPLACE FUNCTION IS_NUMBER (p_input IN VARCHAR2) RETURN NUMBER
AS
BEGIN
  RETURN TO_NUMBER (p_input);
EXCEPTION
  WHEN OTHERS THEN RETURN NULL;
END IS_NUMBER;
/


CREATE OR REPLACE FUNCTION IS_NUMBER (p_input IN VARCHAR2) RETURN NUMBER
AS
BEGIN
  RETURN TO_NUMBER (p_input);
EXCEPTION
  WHEN OTHERS THEN RETURN 0;
END IS_NUMBER;
/