Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Why does NVL always evaluate 2nd parameter

Tags:

sql

oracle

nvl

Does anyone know, why Oracle's NVL (and NVL2) function always evaluate the second parameter, even if the first parameter is not NULL?

Simple test:

CREATE FUNCTION nvl_test RETURN NUMBER AS
BEGIN
  dbms_output.put_line('Called');
  RETURN 1;
END nvl_test;

SELECT NVL( 0, nvl_test ) FROM dual

returns 0, but also prints Called.

nvl_test has been called, even though the result is ignored since first parameter is not NULL.

like image 521
Peter Lang Avatar asked Jan 07 '10 15:01

Peter Lang


1 Answers

It's always been that way, so Oracle has to keep it that way to remain backwards compatible.

Use COALESCE instead to get the short-circuit behaviour.

like image 139
Jeffrey Kemp Avatar answered Sep 23 '22 01:09

Jeffrey Kemp