Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Should/Can I use Sequence for this ?

Re: Should/Can I use Sequence for this ?

From: Noons <wizofoz2k_at_yahoo.com.au>
Date: 28 Sep 2004 00:16:49 -0700
Message-ID: <73e20c6c.0409272316.1ea35cf8@posting.google.com>


krislioe_at_gmail.com (xtanto) wrote in message news:<e1c9bd55.0409270540.104601d3_at_posting.google.com>...
> Hi,
> Each department has its own counter.
> e.g :
> 0100001
> 0100002
> 0200001
> 0200002
> How can I do it with sequence.
>

If you need consecutive numbers then forget sequences: they cannot guarantee consecutive new values.

If you do not need consecutive numbers and assuming you really need an independent sequence for each department, you need to have a stored function (or procedure) that will return the next value of a given department. This function can get the appropriate sequence next value using the department number in a dynamic SQL. Though this is not very scalable.

/If/ high performance/scalability is needed, then I suggest you forget "each department has its own counter" and go with a composite PK that includes a single sequence NEXTVAL and the department number. In a concatenation, like Daniel suggested. Received on Tue Sep 28 2004 - 02:16:49 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US