Oracle ERROR-01722 not showing up consistently-CodePudding

There seems to be inconsistencies with how ERROR-01722 error worked, for those who don't know the issue is due to an invalid number and to fix it you'll need to wrap the number to char.

But when filtering VARCHAR2 it is stated that Oracle will convert the data of the column being filtered based on the value given to it. (see: https://stackoverflow.com/a/10422418/5337433)

Now that this is explained for some reason, the error is inconsistent. As an example I have this query:

In this example filter1 is varchar2

select * 
from table 
where filter1 = 12345 
  and filter2 = '' 
  and filter3 = '';

When this statement run there were no issues, but when you run it like this:

select * 
from table 
where filter1 = 12345 
and filter2 = '';

it errors out to ERROR-01722, im not sure why it is acting this way, and how to fix it.

CodePudding user response：

When you compare a varchar column to a number, Oracle will try to convert the column's content to a number, not the other way round (because 123 could be stored as '0123' or '00123')

In general you should always use constant values that match the data type of the column you compare them with. So it should be:

 where filter1 = '12345'

However if you are storing numbers in that column, you should not define it as varchar - it should be converted to a proper number column.

The reason the error doesn't show up "consistently" is that you seem to have some values that can be converted to a number and some can't. It depends on other conditions in the query if the those values are included or not.

Additionally: empty strings are converted to NULL in Oracle. So the condition filter2 = '' will never be true. You will have to use filter2 is null if you want to check for an "empty" column.