From: "Ranga Chakravarthi" <ranga@cfl.rr.com>
Newsgroups: comp.databases.oracle
References: <2829b61c.0112110959.74d1cc96@posting.google.com>
Subject: Re: Create unique sequence
Lines: 43
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.50.4522.1200
X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4522.1200
Message-ID: <4AzR7.68990$Ga5.11329890@typhoon.tampabay.rr.com>
Date: Wed, 12 Dec 2001 02:48:32 GMT
NNTP-Posting-Host: 24.95.245.47
X-Complaints-To: abuse@rr.com
X-Trace: typhoon.tampabay.rr.com 1008125312 24.95.245.47 (Tue, 11 Dec 2001 21:48:32 EST)
NNTP-Posting-Date: Tue, 11 Dec 2001 21:48:32 EST
Organization: RoadRunner - Central Florida


create a sequence
create sequence myseq increment by 2 start with 2000 nomaxvalue nocache
order;
then, use a BEFORE trigger in the table to test for the condition from the
sequence.

the problem with this approach though is that if for some reason the
transaction rolls back at say
2006, there is no way of getting that number back until the sequence cycles
back to where it started.

if not you can use max(translate(column_name, '0123456789abc',
'0123456789')) + 2

HTH,
Ranga Chakravarthi

"liu" <cissyliu@hotmail.com> wrote in message
news:2829b61c.0112110959.74d1cc96@posting.google.com...
> Hi, all
> Does anyone know how to create a unique oracle sequence like this
> generate sequence in Oracle as follows?
>
> abc2000
> abc2002
> abc2004
> abc2006
> 2008abc
> 2010abc
> 2012abc
> 2014abc
> .
> .
> .
> The number part will start with 2000, incremented by 2.  When the
> sequence reaches 2006, then the sequence reverses the order with text
> string abc in the back followed the number part. Can anyone help me
> come up with an solution.  Thanks a lot!
>
> Liu
>