Q
Problem solve Get help with specific problems with your technologies, process and projects.

Automatic numbering with a sequence, part 2

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

Continued from page 1.

If you always want people to use a sequence when INSERTing new records, you can write a BEFORE INSERT trigger. People doing INSERTs don't have to worry about the sequence syntax, or supplying any value for that column, even if it is defined as NOT NULL. In fact, you can write a trigger such that if they do supply a value for qa_sequence_number, it will be ignored. For example:

CREATE OR REPLACE TRIGGER  table1_before_insert
BEFORE INSERT
ON  table1
FOR EACH ROW
DECLARE
    s   PLS_INTEGER;
BEGIN
    SELECT  table1_seq.NEXTVAL
    INTO    s
    FROM    dual;

    -- :OLD.qa_sequence_number is disregarded
    :NEW.qa_sequence_number := s;
END;
/

Note that you have to treat table1_seq.NEXTVAL as a column of some table (like dual, in the example above). You can't simply say "s := table1_seq.NEXTVAL".

Here are some things to note about sequences.

  • Sequence numbers are not a reliable way to tell the order in which rows were inserted into a table. Use time stamps (like SYSDATE) for this purpose.(You can automate timestamps in a BEFORE INSERT trigger, too.)
  • Gaps may appear in a column populated by a sequence. (For example, if a number is assigned but the INSERT is never committed.)
  • You can use sequence_name.CURRVAL to retrieve the previous number generated in the current session only.
  • ALTER SEQUENCE can not be used to reset the counter. Instead, you have to DROP the sequence and CREATE a new sequence with the same name. (Remember to repeat the GRANT SELECT statements. You do not have to re-create synonyms.)
  • While it's a good idea to associate a sequence with a particular column, Oracle does not automatically make any such association. You can (if you really want to) use one sequence with several columns or several sequences with one column.

Dig Deeper on Using Oracle PL-SQL

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.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close