CODEWITHSUNDEEP
sqloracle-databaseoracle10g
Incognito
Incognito

Reputation: 3094

Behavior of Sequence in Oracle Merge Statements

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,

Upvotes: 10

Views: 9088

Answers (2)

Mohammed Ismail
Mohammed Ismail

Reputation: 81

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

Upvotes: 8

Incognito
Incognito

Reputation: 3094

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

Upvotes: 7

Related Questions