Invalid Oracle NVL Number

Question

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

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

The Oracle documentation for NVLreads:

If expr1 is numeric, then Oracle determines which argument has the highest numerical priority, implicitly converts the other argument to this data type, and returns this data type.

The values ​​for NLS_COMP, NLS_SORTand are the NLS_LANGUAGEsame between the two databases, so they should not cause a difference in the numerical priority of the two arguments. What can be different between these two databases to force 'ok'one to return and an error?

Answer 1

cursor_sharing is probably the key factor.

The predicate "1 = nvl (1," X ")" can be evaluated during parsing if it is always executed as literals and optimized both true and false. However, if cursor_sharing is a force, all three literals can be replaced with other values, and the expression cannot be evaluated before execution.

I needed to use two separate local tables.

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

This is cute ... this is not the answer, but I cannot post it in the comments with any readable clarity.

It all runs on one instance (oracle 11) ... but I think it shows a similar problem.

  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 remember seeing it before, but I cannot remember how to explain it. Oo

, , -, NVL - WHERE SELECT.

Answer 3

NVL 'to_char' "ORA-01722: ":

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