Solved

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

Posted on 2007-11-15
9
1,761 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 300 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 300 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 15

Accepted Solution

by:
ishando earned 200 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 300 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Oracle sql query 7 64
dbms_crypto.decrypt   errors out 6 32
Oracle DBLINKS From 11g to 8i 3 35
oracle date format checking 7 8
Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

825 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