rules repository

G-4230

🆓
Warning

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

ControlPerformance

Rule Details

Severity Level
Critical
Quality Area
Reliability
Fixing times
1 mins
Quick Fix | Local-easy
Database Support
OracleDB (v9.0.1 +)

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

Non-Compliant Example

select nvl(dummy,my_package.expensive_null(value_in => dummy))
  from dual;
Issues
LineColumnMessage
18Use COALESCE instead of NVL.

Compliant Solution - ★★★★★

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

References

← Previous
G-4220
Try to use CASE rather than DECODE.
Next →
G-4240
Always use a CASE instead of a NVL2 command if parameter 2 or 3 of NVL2 is either a function call or a SELECT statement.