Behavior of Sequence in Oracle Merge Statements
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
You can solve this by using a function to increment the value like this
CREATE OR REPLACEFUNCTION seq_nextval_on_demand (p_seq_name IN VARCHAR2) RETURN NUMBERIS 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 thishttp://alex-td.blogspot.in/2012/07/sequences-nextval-in-merge-operator.html