Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!drn.maxwell.syr.edu!news.maxwell.syr.edu!postnews1.google.com!not-for-mail From: krislioe@gmail.com (xtanto) Newsgroups: comp.databases.oracle.server Subject: Re: Should/Can I use Sequence for this ? Date: 27 Sep 2004 20:11:51 -0700 Organization: http://groups.google.com Lines: 45 Message-ID: References: <1096226709.145021@yasure> NNTP-Posting-Host: 203.130.225.47 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit X-Trace: posting.google.com 1096341112 25922 127.0.0.1 (28 Sep 2004 03:11:52 GMT) X-Complaints-To: groups-abuse@google.com NNTP-Posting-Date: Tue, 28 Sep 2004 03:11:52 +0000 (UTC) Xref: dp-news.maxwell.syr.edu comp.databases.oracle.server:225767 Hi, Thanks for the reply. OK, for table-based solution > the app would increment the value in the department row (which unfortunately > serialized transactions at the department level) Is the serializable by default ? Or what command should I use to make sure that no two document use same sequence number ? Thank you, tanto "Mark C. Stock" wrote in message news:... > "xtanto" wrote in message > news:e1c9bd55.0409270540.104601d3@posting.google.com... > | Hi, > | Each department has its own counter. > | e.g : > | 0100001 > | 0100002 > | 0200001 > | 0200002 > | How can I do it with sequence. > | > | Thank you, > | xtanto > | > > you'd need one sequence per department, and then your code would have to > choose which sequence based on the department, plus create a sequence on the > fly for each new department -- plus it looks like you need sequential > numbers with no gaps, and sequences cannot guarantee 'no gaps' > > you'll need a table-based solution, perhaps just keeping the current (or > next) counter value in the department row... then as a new > department-specific sequence number is needed, the app would increment the > value in the department row (which unfortunately serialized transactions at > the department level) and use the calculated value for the new sequence. > note that simply choosing the current max number from the target table (i > believe you said it was for document numbers) would not guarantee > uniqueness, as concurrent transactions could select the same max, and thus > attempt to assign the same new document number > > ++ mcs