Q

Automatic numbering with a sequence, part 1

I have the following SQL statement to insert records into table1 from table2. I am using ROWID from table2 to give

each record a unique sequence number. As an alternative, is it possible within the SQL statement to generate a unique number (i.e. 1, 2, 3, ...., i) for each record that is created on table1?

INSERT INTO table1 
(
    qa_mailing_date_loaded,
    qa_creation_date,
    qa_created_by,
    qa_sequence_number,
    qa_account_number
)
(
    SELECT  ac_date_loaded,
            SYSDATE,
            'Eric Donald',
            ROWID,
            ac_account_number 
    FROM    table2 
    WHERE   ac_completion_date  
                BETWEEN TO_DATE ('2002-11-01','yyyy-mm-dd')
                AND     TO_DATE ('2002-11-30','yyyy-mm-dd')
);

Use a sequence, which is a database object designed for generating numbers in order.
CREATE SEQUENCE  table1_seq
START WITH       1;

sequence_name.NEXTVAL can be thought of as a pseudo-column, like ROWNUM. The first time you refer to the table1_seq.NEXTVAL it will have the value 1; the second time it will be 2; etc. Here's one common example of a sequence in use:

INSERT INTO table1 
(
    qa_mailing_date_loaded,
    qa_creation_date,
    qa_created_by,
    qa_sequence_number,
    qa_account_number
)
(
    SELECT  ac_date_loaded,
            SYSDATE,
            'Eric Donald',
            table1_seq.NEXTVAL,
            ac_account_number 
    FROM    table2 
    WHERE   ac_completion_date  
                BETWEEN TO_DATE ('2002-11-01', 'yyyy-mm-dd')
                AND     TO_DATE ('2002-11-30', 'yyyy-mm-dd')
);

Like other objects, sequences are visible to other users only if the owner grants them privileges. The SELECT privilege gives users the power to generate new numbers:

GRANT  SELECT  ON table1_seq  TO table1_update;

Here are some of the options you can use in a CREATE SEQUENCE statement. Look up the CREATE SEQUENCE command in a SQL reference manual for more details.

  • START WITH n makes the sequence return n (instead of 1) the first time NEXTVAL is used. (Very useful if you want to start using a sequence with a column that already has unique integer values.)
  • INCREMENT BY n causes the sequence to count by n instead of 1. If n is negative, the sequence will generate numbers in descending order.
  • MINVALUE n1 MAXVALUE n2 CYCLECauses the sequence to recycle numbers between n1 and n2 (like a bakery queue)
  • CACHE n generate (but do not assign) numbers in batches of n for faster performance

The options can appear in any order after CREATE SEQUENCE seq_name.

Continued on page 2.

This was first published in November 2003

Dig deeper on Using Oracle PL-SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close