Re: Implementing sequences without sequences

Sadru Fidai wrote:
>
> 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.

You are definitely doing the things in a hard way. Actually what you really need to do is :

1. Create a table identified the database, eg. Server(Pref Char(1)) which contain, 1,2,3,4 or A,B,C,D
2. Create a database sequence or sequence table (like you do).
3. Obtain the unique key by select Server.Pref||seq.next_val from Server; or select Server.Pref||seq.no from Server,Seq for update of Seq.No; (Locking the record is for concurrent operation)

We do this in Replication-Environment, and it of course work.

Hope it helps. Received on Mon Apr 14 1997 - 00:00:00 CDT