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