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

By default (i.e. if you don't say explicitly create sequnce bla cache <some number>) 20 sequence numbers are cached. In case of DB restart you'll lose them. Also in case of rollback you may have gaps. Sequences cannot be used to get _continuous_ numbers, they can be used to get _unique_ numbers.

Gints Plivna
http://www.gplivna.eu Received on Fri Jul 21 2006 - 04:56:08 CDT