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!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

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,611 views