Oracle NVL invalid number

Question

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?

Answer 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

Answer 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');