Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

How do I turn off implicit data conversion on Oracle DB?

Posted on 2007-11-15
9
Medium Priority
?
1,869 Views
Last Modified: 2013-12-19
A table has a column defined as varchar2(30) type.  
A query is being executed against this table as follows:
select * from sv_query where user_id=18612308;
This works in all envrionments except for production.

In production, the query is failing with the following error message:
ORA-01722 : Invalid Number
0
Comment
Question by:kzsk31
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
9 Comments
 
LVL 18

Assisted Solution

by:Jinesh Kamdar
Jinesh Kamdar earned 1200 total points
ID: 20292951
This error would occur if your trying to compare a numeric value against a column that contains non-numeric values as well. Since your user_id is a VARCHAR2, use wrap the value within single-quotes to avoid such errors.

select * from sv_query where user_id='18612308';
0
 

Author Comment

by:kzsk31
ID: 20293000
Thanks, the developers have done this, to resolve the error.

The question still remains why does it work on development and NOT on production?

It seems that it is related to implicit data conversion taking place.  What parameter on the db controls the implicit data conversion?
0
 
LVL 18

Assisted Solution

by:Jinesh Kamdar
Jinesh Kamdar earned 1200 total points
ID: 20293020
>> The question still remains why does it work on development and NOT on production
Can you confirm from DEV & PROD that user_id contains numeric values only in both the instances?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 15

Accepted Solution

by:
ishando earned 800 total points
ID: 20293162
You can't turn off implicit data conversion

Its probably working in DEV because the data is cleaner and none of the fields have character values in them

It is always advisable to enclose values in quotes when querying against VARCHAR2 fields, even if the value of the field is numeric, as the implicit data conversion can prevent use of the index.
0
 

Author Comment

by:kzsk31
ID: 20293229
I am going to confirm to see if the values are all numeric - in both dev and prod.

Important point about the index.

So, wow, I did not know you could not turn off implicit data conversion.

Thx.
0
 

Author Comment

by:kzsk31
ID: 20293350
Jinesh,
How do I check if all values are numeric in the column?
thx
0
 
LVL 18

Assisted Solution

by:Jinesh Kamdar
Jinesh Kamdar earned 1200 total points
ID: 20293402
Fire this query in DEV & PROD on the target table.
If any of them contains any non-numeric values, the query will return an error.

select to_number(user_id) from sv_query order by 1;
0
 

Author Closing Comment

by:kzsk31
ID: 31409435
Thanks a lot for your timely help and expertise
0
 

Author Comment

by:kzsk31
ID: 20293966
Thank you Jinesh and Ishando.

Query ran successfully in dev and had the same error message ORA-01722 in prod.

0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

609 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question