Monday, July 9, 2012

Sequence's NEXTVAL in MERGE operator

I have used MERGE operator many times before. But recently I was surprised by fact that in case when in MERGE there is call of sequence's NEXTVAL it will be executed even the another branch of MERGE operator will be used.

I found remark about this here, section "How to Use Sequence Values"
The reference to NEXTVAL can appear in the merge_insert_clause or the merge_update_clause or both. The NEXTVALUE value is incremented for each row updated and for each row inserted, even if the sequence number is not actually used in the update or insert operation.
Let's illustrate this.

Thursday, July 5, 2012

How to get uninterrupted array of integers or dates using Oracle hierarchical queries

Often in different tasks I need array of integers which does not have any "holes" (for example 1, 2, 3, 4, etc.). Or part of calendar for some period as array of dates.

Usually tables with such arrays are created in database and are used in queries.

But Oracle provides an opportunity to get uninterrupted arrays "on the fly" using hierarchical queries.

Wednesday, July 4, 2012


Now I try to accumulate all my knowledge about these similar functions.
NVL is native Oracle function whereas COALESCE is ANSI SQL one.