Hello everyone,
I don't know if this is an expected behavior but even if the first parameter of NVL is not null, second parameter is evaluated! what I mean is, if your second parameter is a function and your first parameter is not null, oracle runs the function anyway:
drop table tmp1;
create table tmp1(test_data varchar2(800));
create or replace function sf_test1 return varchar2 as
pragma autonomous_transaction;
begin
insert into tmp1 values ('I worked!');
commit;
return 'my function test data';
end;
/
select nvl('test1', sf_test1)
from dual;
select * from tmp1;
I worked!
as you can see result is "I worked!". I always thought that this is like an "if condition" check. if first value is not null then return immediately but it seems there is a unnecessary work here. I looked at doc but couldn't find any information about it. is there anyone aware of that?
my db is 11.2.0.4
also situation has no relation with autonomous_transaction I just used it to show situation.
thanks.
EDIT:
if this is the standard behavior then DECODE could be more useful:
truncate table tmp1;
select decode('test1', null, sf_Test1, 'test1')
from dual;
select * from tmp1;
no rows return
Message was edited by: Mustafa KALAYCI