Tag: Resolution.ORA-01858

Requirement – Executing an sql query and found very weird error.ORA-01858: a non-numeric character was found where a numeric was expected.

Solutions –

the query i am executing

Select Count(*) As Amount, To_Char(Dactiondate, 'YYYY-MM') "month" From Docmeta,Revisions,Workflowhistory,
(Select Ddocname As Docname,Max(Drevisionid) As Revisionid From Revisions Group By Ddocname) A Where  Revisions.Ddocname=A.Docname
And Revisions.Drevisionid=A.Revisionid And  Revisions.Did=Docmeta.Did
And  Workflowhistory.Ddocname=Revisions.Ddocname And Daction='Approve' And ((  Dwfname='InternalBUReviewProcess'
And Trunc(Dactiondate)<Trunc(To_Date(Substr(Substr(Xreviewerdeadline,Instr(Xreviewerdeadline,Dwfstepname,1,1),Instr(Xreviewerdeadline || ',',',',Instr(Xreviewerdeadline,Dwfstepname,1,1),1)), Instr(Substr(Xreviewerdeadline,Instr(Xreviewerdeadline,Dwfstepname,1,1),Instr(Xreviewerdeadline || ',',',',Instr(Xreviewerdeadline,Dwfstepname,1,1),1)),Duser,1,1)+(Length(Duser)) + 2, 8),'MM-DD-YY') ))
AND UPPER(xIsRushRelease) = UPPER('true')
And  Dactiondate Between To_Date ('2013-06' , 'YYYY-MM') And Last_Day(To_Date ( '2013-08 23:59:59' ,'YYYY-MM hh24:mi:ss'))
 
Group By To_Char(Dactiondate, 'YYYY-MM') Order By To_Char(Dactiondate, 'YYYY-MM')

It is working fine when, i am executing in toad.but when i am adding another clause i.e UPPER(xIsRushRelease) = UPPER(‘true’), it start giving this error.Big problem,not able to understand what went wrong.
This sql query is very big.

ORA-01858 occurs in string-to-DATE conversions, when the string being converted doesn’t match the format string. You can expect errors like that whenever you store date information in string columns.

I thought of creating an sql function and checking , where it getting wrong.

i created a function like –

create or replace function getDateNew (DUSER IN varchar2,Dwfstepname IN varchar2,Xreviewerdeadline IN varchar2)
return date is

  l_sysdate date;

begin

 select cast(To_Date(Substr(Substr(Xreviewerdeadline,Instr(Xreviewerdeadline,Dwfstepname,1,1),
Instr(Xreviewerdeadline || ',',',',Instr(Xreviewerdeadline,Dwfstepname,1,1),1)), 
Instr(Substr(Xreviewerdeadline,Instr(Xreviewerdeadline,Dwfstepname,1,1),
Instr(Xreviewerdeadline || ',',',',Instr(Xreviewerdeadline,Dwfstepname,1,1),1)),Duser,1,1)+(Length(Duser)) + 2, 8),'MM-DD-YY')as date) Into L_Sysdate
From Dual;
   Return L_Sysdate;

End;

Ok, now i am using function in my query like

Select Duser,Xreviewerdeadline,Dactiondate,Docmeta.Did,Xbu,Xoccasionevent,Getdatenew(Duser,Dwfstepname,Xreviewerdeadline) as deadline, Trunc(Dactiondate) As Dactiondate From Docmeta,Revisions,
Workflowhistory, 
(Select Ddocname As Docname,Max(Drevisionid) As Revisionid From Revisions Group By Ddocname) A Where  
 Revisions.Ddocname=A.Docname And Revisions.Drevisionid=A.Revisionid And  Revisions.Did=Docmeta.Did 
AND Daction='Approve' And Workflowhistory.Ddocname=Revisions.Ddocname 
And   Dwfname='InternalBUReviewProcess' 
And Trunc(Dactiondate)<Trunc(Getdatenew(Duser,Dwfstepname,Xreviewerdeadline)) 

Its working fine.But when i added another clause , it again giving me same error.I am totally frustrated.Then , i try to handle the exception in my function.There is some exception coming, when i am adding another clause i.e Xbu=Upper(‘H IM SY’)

I need to handle the exception in my function.Finally adding exception part in my query like

create or replace function getDateNew (DUSER IN varchar2,Dwfstepname IN varchar2,Xreviewerdeadline IN varchar2)
return date is

  l_sysdate date;

begin

 select cast(To_Date(Substr(Substr(Xreviewerdeadline,Instr(Xreviewerdeadline,Dwfstepname,1,1),
Instr(Xreviewerdeadline || ',',',',Instr(Xreviewerdeadline,Dwfstepname,1,1),1)), 
Instr(Substr(Xreviewerdeadline,Instr(Xreviewerdeadline,Dwfstepname,1,1),
Instr(Xreviewerdeadline || ',',',',Instr(Xreviewerdeadline,Dwfstepname,1,1),1)),Duser,1,1)+(Length(Duser)) + 2, 8),'MM-DD-YY')as date) Into L_Sysdate
From Dual;
 DBMS_OUTPUT.PUT_LINE(L_Sysdate);
  Return L_Sysdate;
    DBMS_OUTPUT.PUT_LINE(L_Sysdate);
  Exception 
  when others then return null;

End;

After handling exception, it working fine.I handled exception.

Happy coding with Vinay kumar in Techartifact….