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

Home -> Community -> Mailing Lists -> Oracle-L -> Re: sequence generator

Re: sequence generator

From: Regina Harter <rharter_at_emc-inc.com>
Date: Fri, 23 Feb 2001 17:25:22 -0800
Message-ID: <F001.002BC88C.20010223171020@fatcity.com>

At 04:30 PM 2/23/01 -0800, you wrote:
>Can anyone correct/confirm the following:
>
>A sequence generated number is auto committed. i.e if the transaction that
>generates
>the sequence number fails which eventually gets rolledback the generated
>sequence number
>generated is lost and not available for the next transaction.

You are correct, sequences are auto committed.

>Is there by anyway prevent lossing of the sequence generated number in case
>of the
>transaction that generated it fails.Any work around?
>
>The problem is, with this happening finnally the column that will have the
>sequence genarted
>number will not be continuous set of numbers.

Generally, sequences are not good options for values which must be a continuous set of numbers. I know of no good way to recover lost sequence values. On the few occasions that we have required a continuous set of numbers, we have used a separate table which contains the next number in the sequence, which is then updated after use, which operation will of course be rolled back in case of failure. Another option is just to user max + 1 of the column in question, but coding that can sometimes get messy, depending on what else you are doing.

>I needed a second opinion on the above points.
>
>Thanks
>
>Ravindra
>
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>--
>Author: Ravindra Basavaraja
> INET: ravindra_at_sentica.com
>
>Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
>San Diego, California -- Public Internet access / Mailing Lists
>--------------------------------------------------------------------
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
>the message BODY, include a line containing: UNSUB ORACLE-L
>(or the name of mailing list you want to be removed from). You may
>also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Regina Harter
  INET: rharter_at_emc-inc.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Received on Fri Feb 23 2001 - 19:25:22 CST

Original text of this message

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