Inconsistent error ORA-01722: invalid number

2
votes

I got an error with the message "ORA-01722: invalid number" when I executed my query. As digged in, I found out, I will get that error when I use a numeric value for a column that expects a varchar. In my case my query had a case statement like 

CASE WHEN CHAR_COLUMN= 1 THEN 'SOME VALUE' END

But the behavior was different in different instances of same oracle version. The same query worked in one of our dev oracle server, but not in the other. I am curious if there is any configuration that allows oracle to use numeric value to be used in a character column.

PS: The oracle version that we are using is Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

2
oracle
It it possible that both servers just have different data, and your dev server does not have any row with non-numeric strings? – Álvaro González

2 Answers

1
votes

To reproduce the issue:

create table foo (
  CHAR_COLUMN varchar2(10)
);
create table bar (
  CHAR_COLUMN varchar2(10)
);
insert into foo (CHAR_COLUMN) values ('1');
insert into foo (CHAR_COLUMN) values ('2');
insert into bar (CHAR_COLUMN) values ('1');
insert into bar (CHAR_COLUMN) values ('yellow');

Then, when querying against numeric 1, the first query table works and the second doesn't:

select * from foo where CHAR_COLUMN = 1;
select * from bar where CHAR_COLUMN = 1;

When you ask Oracle to resolve this comparison:

WHEN CHAR_COLUMN = 1

... Oracle converts the query internally to:

WHEN TO_NUMBER(CHAR_COLUMN) = 1

In my example, this can be spotted in the explain plan:

---------------------------------------------------------------------
| Id  | Operation           | Name | Rows | Bytes | Cost | Time     |
---------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |    1 |     7 |    3 | 00:00:01 |
| * 1 |   TABLE ACCESS FULL | FOO  |    1 |     7 |    3 | 00:00:01 |
---------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 1 - filter(TO_NUMBER("CHAR_COLUMN")=1)
2
votes

I never rely on implicit data type conversion but always make it explicit:

CASE WHEN CHAR_COLUMN = TO_CHAR(1) THEN 'SOME VALUE' END

Or even not convert at all:

CASE WHEN CHAR_COLUMN = '1' THEN 'SOME VALUE' END

The reason is that Oracle tends to convert the character string to a number, not the other way round. See the example from the linked manual page:

Text Literal Example

The text literal '10' has data type CHAR. Oracle implicitly converts it to the NUMBER data type if it appears in a numeric expression as in the following statement:

SELECT salary + '10'
  FROM employees;