Re: Sequence Numbers

From: <rtproffitt_at_my-deja.com>
Date: Thu, 18 Nov 1999 17:03:26 GMT
Message-ID: <811bgq$fh$1_at_nnrp1.deja.com>


In a similar fashion, you could have one sequence but add an identifier to the front of it, returning a varchar key, which would uniquely identify the source or location or instance (or whatever...) of the data.

example

  1. sequence = myseq
  2. function NextLoaderID return varchar as wId varchar2(xxxxx); begin SELECT 'LDR'||lpad(MySeq.nextval,10,'0') INTO wID from dual; RETURN wID;
  3. function NextWebID (same as (b) except:) SELECT 'WEB'||lpad(MySeq.nextval,10,'0')
  4. Loader uses the NextLoaderID call, Web App uses the NextWebID call.

IDs might look like:
LDR0000000001
LDR0000000002
LDR0000000003
WEB0000000004
WEB0000000005 We used this scheme for unique location identification, so that if we imported into another instance, or merged two instances, there would not be any problem with duplicate sequence-generated numbers. (Interestingly enough, this very thing happened to me in real life: my cell phone company's distribution center merged two locations: Los Angeles and San Diego, and one day I received in the mail a phone I never had ordered... it had been ordered by someone in San Diego, and their computer found my address for delivery. They had a classic "merging-causing-duplicate-ID" problem...)

Robert Proffitt
Beckman Coulter
Brea California

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Nov 18 1999 - 18:03:26 CET

Original text of this message