Skip to content

G-4230: Always use a COALESCE instead of a NVL command, if parameter 2 of the NVL function is a function call or a SELECT statement.

Critical

Efficiency, Reliability

Reason

The nvl function always evaluates both parameters before deciding which one to use. This can be harmful if parameter 2 is either a function call or a select statement, as it will be executed regardless of whether parameter 1 contains a null value or not.

The coalesce function does not have this drawback.

Example (bad)

1
2
select nvl(dummy,my_package.expensive_null(value_in => dummy))
  from dual;

Example (good)

1
2
select coalesce(dummy,my_package.expensive_null(value_in => dummy))
  from dual;