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

Critical

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)

SELECT NVL(dummy, my_package.expensive_null(value_in => dummy))
  FROM dual;

Example (good)

SELECT COALESCE(dummy, my_package.expensive_null(value_in => dummy))
  FROM dual;