Implementing sequences without sequences

Hi,
We are making our application replication-aware. In this process, we have user control the sequence segments. E.G. a sequence in database A may go from 1-100, 201-300 while the same sequence in database be would go from 101-200, 301-400 and so on. If I use table based next number generator, I will have to commit everytime I get next number.

I thought of an idea. Use regular sequence. Once that expires, use DBMS_JOB package to submit a job which will drop and create the sequence. The idea was that, DDL will happen in the separate session without affacting current transaction. Unfortunately, I just learnt today that using DBMS_JOB procedures 'submit' and 'run' perform implicit commit. Oracle support confirmed this. This throws is idea out of the door.

I kicked around other ideas. Like using DBMS_PIPE package but that is not so apealing. This and other ideas need a continous process which we think is not a good idea.

Our implementation is a client/server architecture and use Developer/2000 on the front and Oracle 7.3.X at the backend.

Does anyone have any other suggesion?

Sadru Fidai Received on Mon Apr 07 1997 - 00:00:00 CDT