DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: How to reset the sequence value when a date is changed ? (Urgent)

  1. #1
    Join Date
    Mar 2002
    Posts
    38

    How to reset the sequence value when a date is changed ? (Urgent)

    Hi Guys,

    I have a requirement where my procedure needs to print the invoices for the customers at a routine process daily.
    Assume that this procedure will run n number of times per a day. Inside the procedure I should have a
    default value which has to be printed on the bills. It should have the current system date + counter.
    Example 10072004 || 0001 -----------------> 100720040001

    The value of the counter should be reset to zero when the current system date has been changed.I can use the
    counter from the sequence generator, but how can I reset to zero and how to check the system date.

    Pls give your suggestions.

    Thanks,
    Kishan
    SUROOP B

  2. #2
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    I think you're going to have to serialise on (say) a single-row table containing the EffectiveDate and FirstSequence, in order to deal with the date change & sequence reset. This will only happen once per day so shouldn't cause performance problems (?) - the rest of the time you can just use the sequence.nextval:

    a) check & deal with date change:
    Select * from serial_table
    where EffectiveDate <> trunc(sysdate) for update;
    If you get a row, update it with trunc(sysdate) & my_seq.nextval.

    b) in all cases:
    Build your Invoice number out of: sysdate & (my_seq.nextval - FirstSequence)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width