?
Solved

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

Posted on 2007-11-15
9
Medium Priority
?
1,910 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
  • 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

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

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

571 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