Oracle NVL function always evaluates second parameter

NVL function doesn't do short circuit evaluation  i.e, all parameters are evaluated irrespective of condition but, value is returned only when condition returns true.

Lets test this

Consider below query to generate test data

[sourcecode language="sql"]
SELECT '' col
FROM DUAL
UNION ALL
SELECT     DBMS_RANDOM.STRING ('L', 5) col
FROM DUAL
CONNECT BY LEVEL < 5
[/sourcecode]

it returns 5 rows of which one row is always null

Now, Lets write one simple function which returns some value. We will call this function in case column value is null. Like

[sourcecode language="sql"]
SELECT NVL (tab.col, temp_func (tab.col))
FROM (SELECT '' col
FROM DUAL
UNION ALL
SELECT     DBMS_RANDOM.STRING ('L', 5) col
FROM DUAL
CONNECT BY LEVEL < 5) tab;
[/sourcecode]

The above statement should ideally call function temp_func(defined below) only for one row.

--takes input value and prints it in output
--always returns '***'

[sourcecode language="sql"]
CREATE OR REPLACE FUNCTION temp_func (p_text VARCHAR2)
RETURN VARCHAR2
AS
i   VARCHAR2(30) := 'who ';
BEGIN
i := i || p_text;
DBMS_OUTPUT.put_line (i);
RETURN '***';
END;
[/sourcecode]

But above SELECT statement returns five lines of dbms output, one for each row. That means function temp_func got called for each row irrespective of condition.

Unlike NVL, COALESCE function uses short circuit evaluation and so, prints only one line of dbms output

[sourcecode language="sql"]
SELECT coalesce(tab.col, temp_func (tab.col))
FROM (SELECT '' col
FROM DUAL
UNION ALL
SELECT     DBMS_RANDOM.STRING ('L', 5) col
FROM DUAL
CONNECT BY LEVEL < 5) tab;
[/sourcecode]