CODEWITHSUNDEEP
sqloracle-database
user2785663
user2785663

Reputation:

Oracle SQL: How to generate per-order sequence numbers

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?

Upvotes: 0

Views: 2462

Answers (2)

David Faber
David Faber

Reputation: 12486

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.

Upvotes: 1

benji
benji

Reputation: 606

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);

Upvotes: 3

Related Questions