0

I would like to generate unique sequence numbers for each order comment as they are entered.

For example, the segment:

INSERT INTO order_comments(order_number,
                          seq_num,
                          comment_text,
                          remark_timestamp)
        VALUES (
                  'Smith',
                  (SELECT NVL (MAX (seq_num), 0) + 1
                     FROM order_comments
                    WHERE     order_number='141592'),
                  'Shipped updated version 1.1’,
                  current_timestamp);

can be run multiple times, getting a new sequence number for that user each time.

The bad news is when this is written in Pro*C and we have multiple copies running simultaneously, we seem to get locks if we try to insert two comments for the same user at the same time. We could have an order_comment_sequence and just use nextval in the insert but that seems untidy since the “141592” comments will not have sequential numbers.

For the sake of the current discussion, we will say we have a few million orders, each of which will have five or ten comments. The order_number and seq_num are the primary key.

Is there something wrong with doing it this way? Is there an easier way of doing it?

Improve this question
2
  • 1
    Yes, there's a lot wrong with doing it this way (as you already noticed, this leads to locks, duplicate keys etc.). Using a sequence (as proposed by @benji) is the most sensible approach. Commented Dec 20, 2014 at 7:53
  • 1
    You have a choice - (a) Locks and neat ordering of ids, (b) no locks and untidy sequence numbers. The locks are required to give you the tidy sequence so you need to decide what you need most. Commented Dec 30, 2014 at 2:21

2 Answers 2

Reset to default
3

You can use Oracle Sequence.

First create the sequence, like this:

CREATE SEQUENCE order_comments_seq
  MINVALUE 1
  MAXVALUE 999999999999999999999999999
  START WITH 1
  INCREMENT BY 1
  CACHE 20;

For more info: http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_6015.htm#SQLRF01314

then you can use sequence.NEXTVAL like this:

INSERT INTO order_comments(order_number,
                      seq_num,
                      comment_text,
                      remark_timestamp)
    VALUES ( 'Smith',
              order_comments_seq.NEXTVAL,
              'Shipped updated version 1.1’,
              current_timestamp);
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Just another tip for @Bob , you can then, if needed, grab an individual increment by using: 'SELECT order_comments_seq.NEXTVAL from dual'
The only caveat is that the OP has explicitly rejected this approach. That said, I think it is a good approach; if he needs explicit sequence numbers by order number then he can use ROW_NUMBER() OVER ( PARTITION BY order_number ORDER BY remark_timestamp ) when retrieving data from the table.
1

I don't think you can avoid the "untidy" approach if you want to avoid locks. You can generate a sequence number by ORDER_NUMBER when retrieving data from the database as follows:

SELECT order_number, ROW_NUMBER() OVER ( PARTITION BY order_number ORDER BY remark_timestamp ) AS seq_num
  FROM order_comments

The "sequence" approach given by @benji is one approach. Another is to generate a random number using SYS_GUID():

TO_NUMBER( SYS_GUID(), 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX' )

You could even use this on the SEQ_NUM column as a default:

ALTER TABLE order_comments MODIFY seq_num DEFAULT TO_NUMBER( SYS_GUID(), 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX' );

Hope this helps.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.