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

Home -> Community -> Usenet -> c.d.o.misc -> Re: Auto-increment sequence

Re: Auto-increment sequence

From: Martin Doherty <martin.doherty_at_elcaro.moc>
Date: Thu, 14 Nov 2002 12:14:05 -0800
Message-ID: <2yTA9.14$8Q5.41@news.oracle.com>


>
>
>>Mike Trozzo wrote
>>
>>>Hi,
>>>
>>>I wrote a sequence to generate unique ID numbers for a table, along with a
>>>before insert trigger. I'm also writing an application in Delphi to access
>>>this database. I'm using 9i and Delphi 5.
>>>
>>>My problem is this: When I run the insert procedure, and come up against a
>>>not null violation, the sequence increments anyway, and the next time I
>>>successfully insert a record, the previous number is skipped. How do I
>>>avoid this problem and keep the number in sequence?
>>>
>>>Thanks,
>>>Mike Trozzo
>>>
>>>
>>>
>>>
>>>
>>>
>
> Martin Doherty wrote:
>
>>Mike, see my posting earlier today to Owen Gibbons "Re: setting a
>>default ID with BC4J Entity".
>>
>>If you use a sequence object to generate keys, gaps *will* occur. May I
>>ask why you need your numbers to be in unbroken sequence? If your only
>>requirement is uniqueness, a gap shouldn't cause a problem. If your
>>auditors get into a panic because there is no record of Purchase Order #
>>1234, you'll have to rethink your approach to generating IDs.
>>
>>Martin
>>
>>
>Daniel Morgan wrote:
>
>
>The simple solution to what Martin has pointed out is as follows:
>
>BEGIN
> INSERT INTO target_table using NEXTVAL
>EXCEPTION
> WHEN OTHERS THEN
> INSERT INTO error_log using CURRVAL
>END;
>
>That way no number is ever lost and you have a complete auditable trail.
>
>Daniel Morgan
>
>

I can't agree 100% with Daniel, it will *reduce* the instances of lost numbers but the INSERT INTO error_log may also get rolled back or fail under some circumstances.

Martin Received on Thu Nov 14 2002 - 14:14:05 CST

Original text of this message

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