Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

NVL function calculates second parameter "always"

Mustafa KALAYCIOct 3 2017 — edited Oct 4 2017

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

This post has been answered by Solomon Yakobson on Oct 3 2017
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Nov 1 2017
Added on Oct 3 2017
17 comments
2,712 views