Results 1 to 4 of 4

Thread: Sequences Reg.

Hybrid View

Previous Post Previous Post   Next Post Next Post
  1. #1
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    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?


  2. #2
    Moderator
    Join Date
    Jun 2007
    Answers
    2,074

    Re: Sequences Reg.

    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.


  3. #3
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: Sequences Reg.

    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".


  4. #4
    Junior Member
    Join Date
    Feb 2008
    Answers
    28

    Re: Sequences Reg.

    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


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
About us
Applying for a job can be a stressful and frustrating experience, especially for someone who has never done it before. Considering that you are competing for the position with a at least a dozen other applicants, it is imperative that you thoroughly prepare for the job interview, in order to stand a good chance of getting hired. That's where GeekInterview can help.
Interact