'Oracle NVL invalid number

I have two Oracle 12c (12.1.0.2.0) databases, one of which returns 'ok' for the following query (using SQL Developer 3.2.20.10), whereas the other results in ORA-01722: invalid number:

select 'ok' from dual where 1 = nvl(1, 'X');

The Oracle documentation for NVL says:

If expr1 is numeric, then Oracle determines which argument has the highest numeric precedence, implicitly converts the other argument to that datatype, and returns that datatype.

The values for NLS_COMP, NLS_SORT and NLS_LANGUAGE are the same between the two databases, so they shouldn't be causing a difference in the numeric precedence of the 2 arguments. What could be different between these two databases to cause one to return 'ok' and one to error?

sqloracle


Solution 1:[1]

cursor_sharing is likely the key factor.

The predicate "1 = nvl(1, 'X')" can be evaluated at parse time if it will always be executed as literals and optimized as either true or false. However if cursor_sharing is force then all three literals can be substituted for other values and the expression can't be evaluated until execution.

I had to use two separate local tables to test it.

alter session set cursor_sharing=force;
create table me_dual as select * from dual;
select 'ok' from me_dual x where 1 = nvl(1, 'A');
select 'ok' from me_dual x where 1 = nvl(1, 'A')

ERROR at line 1:
ORA-01722: invalid number
                                               *
alter session set cursor_sharing=exact;
create table alt_dual as select * from dual;
select 'ok' from alt_dual x where 1 = nvl(1, 'A');

'O
--
ok

Solution 2:[2]

wrapping the column parameter for NVL within 'to_char' resolved my issue with the 'ORA-01722: invalid number' error:

select 'ok' from dual where 1 = nvl(to_char(1), 'X');

Solution 3:[3]

That's cute ... this isn't an answer, but I can't post this in comments with any sort of readable clarity.

This is all run on same instance (oracle 11) ... but I think it shows similar issue.

  SQL> select version from v$instance;

  VERSION
  -----------------
  11.2.0.4.0

  SQL> select * from dual where 1 = nvl(1,'X');

  D
  -
  X

  SQL> select nvl(1,'X') from dual;
  select nvl(1,'X') from dual
               *
  ERROR at line 1:
  ORA-01722: invalid number


  SQL>

I recall seeing this before, but can't remember how to explain it. O.o

Not sure why it seems to evaluate NVL differently between WHERE and SELECT clauses.

Solution 4:[4]

I know many are facing issue like NVL: INVALID NUMBER in NVL Function in Oracle Database.

SELECT NVL(1,'X') FROM dual;
             *
ERROR at line 1:
ORA-01722: invalid number

Here's the explantion and solution

Oracle implicitly converts the second argument in NVL() to same datatype as the first argument. For more information please dig here.

So, the solution is to convert the first argument to char datatype using to_char()

SELECT NVL(to_char(1), 'X') FROM dual;

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Gary Myers
Solution 2 slfan
Solution 3 Ditto
Solution 4 Umakanth Sahu

Related Questions