Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Vinay.

Asked: August 29, 2017 - 9:48 pm UTC

Last updated: August 30, 2017 - 6:17 am UTC

Version: 11.2.0.4.0

Viewed 1000+ times

You Asked

Hi Tom,

Can you explain why NVL has to process expr2 when expr1 is not null?
I see the error is not occuring when used in WHERE condition.

Error:-
UPDATE mytable
SET number_col = nvl(1234,'dummy')
where number_col is null
or
select nvl(1,'X') from dual;

Success:-
select * from dual where 1 = nvl(1,'X');

Thanks,
Vinay

and Connor said...

That's just how it is, eg

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


You can work around this with COALESCE but there's two things at play here

1) consistency of data types
2) evaluation of expressions

If you dont keep datatype consistency, you can still get into trouble:

SQL> select coalesce(1,'X') from dual;
select coalesce(1,'X') from dual
                  *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected NUMBER got CHAR


but if you do have consistent datatypes (which I imagine you should) then things will work out

SQL> select nvl(1, 1/0) from dual;
select nvl(1, 1/0) from dual
               *
ERROR at line 1:
ORA-01476: divisor is equal to zero


SQL> select coalesce(1, 1/0) from dual;

COALESCE(1,1/0)
---------------
              1



Is this answer out of date? If it is, please let us know via a Comment
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.