Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Exhaused sequence

RE: Exhaused sequence

From: Leonard, George <>
Date: Fri, 18 Feb 2005 08:13:23 +0200
Message-ID: <>

Does it have to be type number,

If not consider using you function but return only 5 digits and prepend a 'a' to it,

Basically when done with the a you can move to be, this opens up another 26 X 99999=20

George Leonard
Oracle Database Administrator
New Dawn Technologies @ Wesbank

You Have The Obligation to Inform One Honestly of the risk, And As a Person
You Are Committed to Educate Yourself to the Total Risk In Any Activity! Once Informed & Totally Aware of the Risk, Every Fool Has the Right to Kill or Injure Themselves as They See Fit!

-----Original Message-----
[] On Behalf Of Knight, Jon Sent: 17 February 2005 23:55 PM
To: ''
Subject: Exhaused sequence

=20 We have a sequence that is about to reach it's maximum value (999999)=
Ordinarly, I would just expand the column and let it keep going, but it populates a business column. They don't care what the value is, as long as
it's unique and no longer than 6 digits. Of course, like many sequence populated columns, it's not consecutive.

=20 I'd like to go back and fill in those "gaps" while the business users=

decide what they want to do. So, I'm thinking: reset the sequence to zero &
create a function that calls nextval until it finds one that's available.
Besides a performance hit, are there any other gotchas I'm missing? Scalability?

=20 Has anyone done something similar before?

Jon Knight


The views expressed in this email are, unless otherwise stated, those of =
the author and not those
of the FirstRand Banking Group an Authorised Financial Service Provider o=
r its management.
The information in this e-mail is confidential and is intended solely for=

=20the addressee.
Access to this e-mail by anyone else is unauthorised. If you are not the intended recipient, any disclosure, copying, distribut= ion or any action taken or=20 omitted in reliance on this, is prohibited and may be unlawful. Whilst all reasonable steps are taken to ensure the accuracy and integrit= y of information and data=20 transmitted electronically and to preserve the confidentiality thereof, n= o liability or=20 responsibility whatsoever is accepted if information or data is, for what= ever reason, corrupted=20 or does not reach its intended destination.
=20 ________________________________
Received on Fri Feb 18 2005 - 01:16:27 CST

Original text of this message