Solved

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

Posted on 2007-11-15
9
1,736 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
 
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
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.

 

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

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.

Join & Write a Comment

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to take different types of Oracle backups using RMAN.

757 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now