Behavior of Sequence in Oracle Merge Statements

Question

Recently, I came across an issue related to incrementing sequence values inside a merge statement.

The MERGE INSERT clause, is accessing a sequence to populate one of the columns. I noted that, it does not matter how many rows are actually eligible for insertion, the sequence ends up incrementing for the total number of records in the source SELECT clause.

Why that may be happening?

I am working on Oracle 10gR2

Thanks,

Answer 1

I have found the answer. As per Oracle documentation, the sequence will be incremented for each merged record and it does not matter how many records are actually inserted.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/pseudocolumns002.htm#sthref809

Answer 2

You can solve this by using a function to increment the value like this

CREATE OR REPLACE
FUNCTION seq_nextval_on_demand (p_seq_name  IN  VARCHAR2)
  RETURN NUMBER
IS
  v_seq_val  NUMBER;
BEGIN
  EXECUTE IMMEDIATE 'select ' || p_seq_name || '.nextval from dual'
     INTO v_seq_val;

  RETURN v_seq_val;
END seq_nextval_on_demand;

the logic is function is called only when "insert" branch of merge statement is really used.

for further refer this http://alex-td.blogspot.in/2012/07/sequences-nextval-in-merge-operator.html