RE: Exhaused sequence

From: "Knight, Jon" <jknight@xxxxxxxxxxxxxx>
To: "'Davey, Alan'" <ddavey@xxxxxxxxxx>, "'Oracle-L@xxxxxxxxxxxxx'" <Oracle-L@xxxxxxxxxxxxx>
Date: Thu, 17 Feb 2005 16:20:20 -0600

Thanks for the suggestion.  I should have mentioned that it's a client's
account number.  So, the existing ones have to stay the same.

Thanks,
Jon Knight

 -----Original Message-----
From:   Davey, Alan [mailto:ddavey@xxxxxxxxxx] 
Sent:   Thursday, February 17, 2005 4:15 PM
To:     jknight@xxxxxxxxxxxxxx
Subject:        RE: Exhaused sequence

When you say that they don't care what the value is, does that mean if
the current value is say '000666', you can modify it to something else
like '000777'?

If so, then update all the record values sequentially starting at
'000001' through '0nnnnn'.  Then reset your counter to the max value
plus one.

-----------------------------------------
Alan Davey
Sr. Programmer/Analyst, Advertising Solutions
Oracle 9i OCA; 3/4 OCP
Harris Corporation
212.295.3458


> -----Original Message-----
> From: oracle-l-bounce@xxxxxxxxxxxxx 
> [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Knight, Jon
> Sent: Thursday, February 17, 2005 4:55 PM
> To: 'Oracle-L@xxxxxxxxxxxxx'
> Subject: Exhaused sequence
> 
>   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.
> 
>   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?
> 
>   Has anyone done something similar before?
> 
> Thanks,
> Jon Knight
> --
> //www.freelists.org/webpage/oracle-l
> 
--
//www.freelists.org/webpage/oracle-l

RE: Exhaused sequence

Other related posts: