Re: Strange problem with Sequence.. please help..

xtanto_at_hotmail.com wrote:
> HI Gurus,
>
> I develop application on Oracle 10.1.0.2.0 n Win2003. I am creating
> Sequence for auto generation of document number column and that column
> is part of PK. I put the logic to access the sequence on a Trigger
> (attached below).
>
> I have strange problem, it already occur several times, my document
> number "jump" not to next sequence number. .e.g: ..32,33,55 (???)
>
> What can cause this problem ?
> Can it be because Export / Import ?
>
> Below is my Trigger :
> CREATE OR REPLACE TRIGGER TBI_INVOICE BEFORE INSERT ON INVOICE
> FOR EACH ROW
> BEGIN
> IF :NEW.NODOKUMEN IS NULL OR :NEW.NODOKUMEN < 0 THEN
> IF (:NEW.COMPID = 'ASP01') THEN
> SELECT INVOICE_ASP_IV_SEQ.NEXTVAL INTO :NEW.NODOKUMEN FROM DUAL;
> ELSIF (:NEW.COMPID = 'SIO01') THEN
> SELECT INVOICE_SIO_IV_SEQ.NEXTVAL INTO :NEW.NODOKUMEN FROM DUAL;
> ELSIF (:NEW.COMPID = 'SIU01') THEN
> IF :NEW.docid = 'IV' THEN
> SELECT INVOICE_SIU_IV_SEQ.NEXTVAL INTO :NEW.NODOKUMEN FROM DUAL;
> ELSIF :NEW.docid = 'TA' THEN
> SELECT INVOICE_SIU_TA_SEQ.NEXTVAL INTO :NEW.NODOKUMEN FROM DUAL;
> ELSIF :NEW.docid = 'TR' THEN
> SELECT INVOICE_SIU_TR_SEQ.NEXTVAL INTO :NEW.NODOKUMEN FROM DUAL;
> END IF;
> END IF;
> END IF;
> END;
>
> Thank you for your help,
> xtanto

Typically, the sequence numbers are cached in groups of 20 when created with the default options. If you restart the Oracle instance/database the cached numbers will be lost. If the sequence .NEXTVAL would have pulled a value of 6, after restarting the Oracle instance/database the .NEXTVAL would pull 21. The number of sequence numbers cached can be adjusted, but may impact performance if set too low. If your code executes a .NEXTVAL and then performs a rollback, Oracle does not rollback the sequence to the previous number, since another session may have already executed a .NEXTVAL for the same sequence.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Fri Jul 21 2006 - 05:04:53 CDT