NVL() does not short circuit

Here's an interesting tidbit about NVL(expr1,expr2).  The documentation states that " If expr1 is null, then NVL returns expr2. If expr1 is not null, then NVL returns expr1." What you may not know is that both expr1 and expr2 are evaluated even if expr1 is not null. NVL does not short circuit. Fortunately alternative methods like COALESCE and the CASE statement will.

DECLARE
  str VARCHAR2(1);

  FUNCTION dummy_function RETURN VARCHAR2
  IS
  BEGIN
    DBMS_OUTPUT.PUT('(Function ran)');
    RETURN 'X';
  END dummy_function;
BEGIN
  str := 'A';

  DBMS_OUTPUT.PUT_LINE('NVL: '||NVL(str,dummy_function));
  DBMS_OUTPUT.PUT_LINE('COALESCE: '||COALESCE(str,dummy_function));
  DBMS_OUTPUT.PUT_LINE('CASE: '||CASE WHEN str IS NOT NULL THEN str ELSE dummy_function END);
END;
/
(Function ran)NVL: A
COALESCE: A
CASE: A


PL/SQL procedure successfully completed.

This is definitely something to keep in mind if, like in the example above, you are calling a function for expr2. If that function does a significant amount of processing and/or the NVL() is inside of a loop with many iterations, the cycles it takes to execute the function every time could add up.