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 last published in November 2003

Dig Deeper on Using Oracle PL-SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

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.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close