Re: Create unique sequence

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_at_hotmail.com> wrote in message news:2829b61c.0112110959.74d1cc96_at_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
>
Received on Wed Dec 12 2001 - 03:48:32 CET