Re: Finding next number in custom sequence

"compboy" <compboy80_at_hotmail_spam.com> wrote in message news:<ude3l3jtf4a370_at_corp.supernews.com>...
> I have a table that has information in it that represents a message:
>
> DESTINATION VCHAR(20)
> SEQ_NUM NUMBER(4,0)
> PAYLOAD LONG RAW
>
> *note each destination has it own SEQ_NUM sequence.
>
> I have a program that uses the table to store messages while it is trying to
> send them. When my server is shutdown for some reason I need to be able to
> find the next sequence number available so that I can start the message
> numbering all over again when the program starts up again. The sequence
> number is from 1-2047. The problem I am having is when the following
> situation occurs with the sequence numbers:
> DESTINATION SEQ_NUM
> Joe 2045
> Joe 2046
> Joe 2047
> Joe 1
> Joe 2
> Joe 3
> ...
> Mark 100
> Mark 101
> Mark 102
>
> How can I write my select statement to find that the next number for Joe is
> 4. When there is no loop through, I was just finding the largest SEQ_NUM
> with MAX(SEQ_NUM) and adding 1 to it.
> SELECT DESTINATION,MAX(SEQ_NUM),MIN(SEQ_NUM) FROM IASPROCESSING GROUP BY
> DESTINATION
>
> I am using Oracle 8.1.6i
>
> I didn't want to use an Oracle sequence because I have 4000+ different
> destinations and I didn't want to create that many sequences.
>
> TIA
This is not a direct answer to your question, but here is another way of accomplishing this task that you may want to consider.

Having to reconstruct the next sequence on restart seems like a fairly time consuming process. I would switch my process to use an anonymous transaction contained in a function that access and updates an application sequence table where the key is the message destination and the only column is the sequence value. Fetch for update, add +1, check for wrap, adjust if necessary, update, and return value. Then on restart no special logic is required to find the next sequence to use. You may still have need some logic for how to handle stored, but unsent messages but at least the next sequence value will be easy.

Warning, you cannot use anonymous transactions from distributed transactions (at least in 8).

HTH -- Mark D Powell -- Received on Tue May 07 2002 - 08:31:48 CDT