CODEWITHSUNDEEP
sqloracle-databaseoracle11gtype-conversion
VJS
VJS

Reputation: 2951

Oracle DB Query Issue : Same query different behaviour

Below is my Query. In one Oracle DB (11.2.3.0) this is giving me error but in other same Oracle DB below query is working fine. Not able to understand this different behavior.

Output from one DB

SQL> select id  from cag where greeting_id=1111100001 for update;

ERROR at line 1:
ORA-01722: invalid number

Output from other DB

SQL> select id from cag where greeting_id=1111100001 for update;

ID - 9111100001

Is there any DB internal setting?

Upvotes: 0

Views: 116

Answers (1)

Alex Poole
Alex Poole

Reputation: 191265

You are storing numbers in a varchar2 column. That is not good practice, for many reasons including performance, but particularly because it causes data issues like this.

If the column is only ever supposed to contain numbers then make it an actual number column.

If it is a general column that is expected to hold numeric and other data then you should really reconsider the design anyway and put the different types of data in separate columns of the right type. It looks like it's expected to always be a number though - you wouldn't expect an ID to have a varying type.

In the meantime, to query a string column you need to avoid implicit conversion. At the moment when you do:

select id  from cag where greeting_id=1111100001;

the database has to convert every string in the greeting_id column into a number before comparing it with 1111100001, and it is that implicit conversion from string to number that is throwing the error - because you have values in the column that cannot be implicitly converted. In one database all the column values can be converted; on the other at least one value cannot. If you aren't expecting that then look at the data to see if someone has stored something unexpected like 'none' or 'invalid' or 'unknown', or even a decimal fraction with the wrong decimal separator.

If you have to stick with a string when look for a string - compare column data with values and literals of the same type:

select id  from cag where greeting_id='1111100001';

The correct approach is to make the column the correct data type though.

Upvotes: 2

Related Questions