Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Please ask technical questions in the appropriate category. Thank you!

coalesce evaluating all arguments?

Hello out there,
I'm stumbling over some strange behaviour of COALECE. Have a look on this:

create sequence seq_test start with 1;

select coalesce(dummy,to_char(seq_test.nextval)) test,seq_test.currval from dual;

When I repeatedly run this SQL, currval keeps increasing.

I'm Using Oracle 19.21.0.0.0 and Oracle 19.19.0.0.0

Is this a bug or does this just apply to sequences? How do I call this without the sequence increasing even if the first argument is not null?

I tried nvl and case without success.

This post has been answered by mathguy on May 15 2024
Jump to Answer
Comments
Post Details
Added on May 15 2024
9 comments
161 views