CODEWITHSUNDEEP
sqloracle-databasenvl
Peter Lang
Peter Lang

Reputation: 55584

Why does NVL always evaluate 2nd parameter

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.

Upvotes: 8

Views: 2974

Answers (4)

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60292

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.

Upvotes: 9

Doug Porter
Doug Porter

Reputation: 7897

Here is a post where Tom Kyte confirms that decode and case short circuit but not nvl but he doesn't give justification or documentation for why. Just states it to be:

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:926029357278#14932880517348

So in your case you should use decode or case instead of nvl if an expensive function will be called in your query.

Upvotes: 5

Tony Andrews
Tony Andrews

Reputation: 132630

In general, it would make sense that the second parameter is evaluated before calling the function, because in general that is how functions are called: all arguments to the function are evaluated and the evaluated values are sent to the function.

However, in the case of a very common system function like NVL, I would have thought PL/SQL could optimise, treating the function call as a special case. But perhaps that is more difficult than it sounds (to me), as I'm sure this optimisation would have occurred to the developers of Oracle.

Upvotes: 3

Jonas Lincoln
Jonas Lincoln

Reputation: 9767

They are obviously not short-circuiting, but I can't find any references in Oracle documentation.

Check out this discussion: http://forums.oracle.com/forums/thread.jspa?messageID=3478040

Upvotes: 0

Related Questions