• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1955
  • Last Modified:

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

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
kzsk31
Asked:
kzsk31
  • 5
  • 3
4 Solutions
 
Jinesh KamdarCommented:
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
 
kzsk31Author Commented:
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
 
Jinesh KamdarCommented:
>> 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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
ishandoCommented:
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
 
kzsk31Author Commented:
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
 
kzsk31Author Commented:
Jinesh,
How do I check if all values are numeric in the column?
thx
0
 
Jinesh KamdarCommented:
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
 
kzsk31Author Commented:
Thanks a lot for your timely help and expertise
0
 
kzsk31Author Commented:
Thank you Jinesh and Ishando.

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

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now