Sequences with MERGE statements

[jovery]

Well isn't that better? ;-)

I tried a quick test in 9.2.0.6 and the sequence was not touched. Of course there is the question of how many times it is touched for each row that is inserted.

Odd when I try that same test it works fine, the only difference between the code I posted and the live code is that the data is gather over a database link in the live example. Here is a sample run using the funtion method:

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
With the Partitioning option
JServer Release 9.2.0.5.0 - Production

SQL> SELECT databases_users_s.nextval
   2   FROM dual;

   NEXTVAL
----------
      3306

SQL> exec dbspy.scan('C','A');

PL/SQL procedure successfully completed.

SQL> SELECT  databases_users_s.nextval
  2  FROM dual;

   NEXTVAL
----------
      3363

So in this case the sequence advanced by 56 (taken off 1 for my second select), the procedure scans 28 databases which means the sequence is advanced by 2 for each individual execution. Only 27 databases are contatced via links which means that in the packaged example the sequence increases in the same way unlike the sample code. Again no rows are added so the sequence should not advance.

Although this is better than the original code it still means that over the day there will be over 1500 sequence advances for no reason as this package is run on a fairly regular basis (part of a monitoring tool)

Have you considered setting the sequence in a BEFORE INSERT trigger ?

Yes I had considered using a trigger, which is something I may have to resort to but I'd like to know why this occurs.

Thanks for the help so far, any more suggestions what could be causing this weird result?