1

Calling NVL and NVL2 function with a stored function. Function gets called even when value is not to be returned from that function. Please see following example.

create or replace
 function f1
      return varchar2
     is
    begin
        ---set serveroutput on
         dbms_output.put_line('Call to F1');
         return 1;
  end;

set serveroutput on

select  NVL2(null,f1,100)   from  dual


NVL2(NULL,F1,100)                                                              
--------------------------------------------------------------------------------
100                                                                             
Call to F1

select nvl('x',f1) from dual

NVL('X',F1)                                                                    
--------------------------------------------------------------------------------
x                                                                               
Call to F1

So though the correct value is returned from the NVL and NVL2 function but function is also called.

This logic is used in many places and causing extra calls to the function causing performance degradation. Any help is appreciated.

Oracle version is Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

atokpas
  • 3,231
  • 1
  • 11
  • 22
oravib
  • 11
  • 1

0 Answers0