Thread: Sequences Reg.

I am generating invoice number in the following way.

SELECT MAX(DOCNO) INTO V_CODE FROM DSL_ISSUE_TRANSACTION;
IF V_CODE IS NULL THEN
V_CODE := '000001';
ELSE
V_CODE := LPAD(V_CODE+ 1,6,'0');
END IF;

Even if we delete last record, it will select max(docno) and then generate continuous number.

In sequences where does it stores its current value? If we delete last record does it consider existing last number as current value or not?

Is there any additional benefit if we use sequences in the above situation instead of generating the invoice number manually?

1.In sequence the current value is stored in the dictionary object user_sequences.
2.No
3.Since no manual operation less time for generation. If cache is used even faster generation of number.

But the downside of creating a sequence with a cache is that if a system failure occurs, all cached sequence values that have not be used, will be "lost".

hi

You are right, when we use cache with sequences, we miss some values bcoz of instance shutdown and 'age out' of shared pool ( which contains the cached sequences ).

here is some trade off.

If we need to minimize the IO contention, we can use cache. ( Why bcoz, in non cache mode, every time sequence will read disk for current value)

Otherwise, if we don't like to loss even a single value returned by the sequence, we shouldn't use cache option.

you can get exact article about this topic.



here.http://www.arikaplan.com/oracle/ari82599.html


Jaya