Monday, February 14, 2011

Getting "ORA-01722: invalid number " while using NVL function

This error occurs if the first and the second parameter are different datatype, example is show below:


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

SQL> select nvl(1,2) from dual;
  NVL(1,2)
----------
         1
SQL> select nvl('jeet','oracle') from dual;
NVL(
----
jeet
SQL>
SQL>  select nvl(sysdate,'characters') from dual;
 select nvl(sysdate,'characters') from dual
                    *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected

SQL> select nvl(sysdate,123) from dual;
select nvl(sysdate,123) from dual
                   *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected DATE got NUMBER

SQL> select nvl(sysdate, to_date('01-01-11','DD-MM-YY')) from dual;
NVL(SYSDA
---------
14-FEB-11





No comments: