Handling – ORA-01858: a non-numeric character was found where a numeric was expected

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

SQL Optimization Tips/Questions

Below are some of the tips for SQL query optimizations in the form of question/answers.

1. Which of the following query is the most optimized?
a. SELECT column_name FROM table_name WHERE LOWER(column_name) = ‘name’.
b. SELECT column_name FROM table_name WHERE column_name = ‘NAME’ or column_name = ‘name’.
c. SELECT * FROM table_name WHERE LOWER(column_name) = ‘name’
d. SELECT * FROM table_name WHERE column_name = ‘NAME’ or column_name = ‘name’.

Answer: B.
Reason: We should specify the columns in the Select queries and avoid functions like UPPER, LOWER etc as far as possible.

2. Which of the following query generally prevents (but not always) the query optimizer from using an index to perform a search?
a. SELECT member_number, first_name, last_name FROM members WHERE firstname like ‘m%’
b. SELECT member_number, first_name, last_name FROM members WHERE dateofbirth < DATEADD(yy,-21,GETDATE())
c. SELECT member_number, first_name, last_name FROM members WHERE DATEDIFF(yy,datofbirth,GETDATE()) > 21
d. All of these

Answer: C
Reason: Column name is mixed within a function. Hence index cannot be used by optimizer.

3. When we use “NOT IN” our SQL queries, the query optimizer uses which technique to perform the activity?
a. Indexing
b. Clustered Indexed scan
c. Nested table scan
d. None of these

Answer: C

4. Which of the following is the best way of inserting a binary image into database?
a. Use Insert statement
b. Use Stored procedure
c. Both give same performance
d. None of these

Answer: B
Reason: The reason for this is because the application must first convert the binary value into a character string (which doubles its size, thus increasing network traffic and taking more time) before it can be sent to the server. And when the server receives the character string, it then has to convert it back to the binary format (taking even more time).

5. SELECT INTO option locks the system tables. True or false?
Answer: True

6. Using which among “Derived table” and “Temporary table” can we reduce I/O and boost our application’s performance?
Answer: Derived table
Reason: A derived table is the result of using a SELECT statement in the FROM clause of an existing SELECT statement. By using derived tables instead of temporary tables, we can reduce I/O and boost our application’s performance.

7. Which of the following query is the best one in performance to verify the existence of a record in a table:
a. SELECT COUNT(*) FROM table_name WHERE column_name = ‘xxx’
b. IF EXISTS (SELECT * FROM table_name WHERE column_name = ‘xxx’)
c. Both give same performance

Answer: B
Reason: don’t use SELECT COUNT(*) in your Transact-SQL code to identify it, which is very inefficient and wastes server resources.

8. Which of the following is NOT recommended for UPDATE queries in order to reduce the amount of resources required to perform the query:
a. Try not to change the value of a column that is also the primary key.
b. Try to update a column that has a reference in the WHERE clause to the column being updated whenever possible
c. Try to avoid updating heavily indexed columns.
d. When updating VARCHAR columns, try to replace the contents with contents of the same length.

Answer: B
Reason: All points except “B” are recommended points for UPDATE queries. Instead we should try not to update a column that has a reference in the WHERE clause to the column being updated.

9. Omitting of which clause (if possible) can decrease the possibility that a sort operation will occur:
a. DISTINCT
b. ORDER BY
c. LIKE
d. Both A and B

Answer: D

10. Which of the following query will be better in performance?
a. SELECT * FROM Orders WHERE OrderID*3 = 33000
b. SELECT * FROM Orders WHERE OrderID = 33000/3
c. Both are same in terms of performance as well.

Answer: B
Reason: We should avoid computation on columns as far as possible and hence we will get an index scan instead of a seek.

kick it on DotNetKicks.com

Shout it

pimp it