Question:
I am converting an application from Microsoft SQL Server to Oracle. How do I define a column to be an autonumber column in Oracle ?
Answer:
I gather from the more detailed descriptions of this question the SQL Server allows a numeric column to be defined with a 'default' mechanism that generates a new sequential number every time a new row is inserted into the table. I do not know if this number is lost if a transaction rolls back or if there is some mechanism to resurrect such numbers. I presume the number generated is there to act as a meaningless unique key.
There is no exact equivalent in Oracle. Default values are limited to constants, or 'pseudo-constants' such as the current user id, current date and time. There is, however, a mechanism that is similar in effect to the SQL Server autonumber. This mechanism requires the creation of a SEQUENCE and a TRIGGER.
A SEQUENCE is an Oracle object that can be used outside the scope of a transaction as a high-speed mechanism for generating a sequence of numbers. This sequence of numbers may be cyclic, can be ascending or descending, can be bounded or unbounded, and can have a limit. There is a reference document about sequences (based on Oracle 7.3) already available on this site.
A TRIGGER is a pre-compiled piece of PL/SQL code that executes when certain actions take place that meet required conditions. Under Oracle 7, TRIGGERS could only be associated with DML statements made against tables, but the range of options has expanded under Oracle 8.
An 'autonumber' column then needs you to do two things. Create a SEQUENCE as the source of the numbers, that you will store in the column, and create a TRIGGER, attached to the table, which fires every time you insert a row; the trigger will get the next available sequence number and write it into the column. A code sample follows:
Sample of using Sequences and Triggers
create table auto_numb(
n1 number primary key,
v1 varchar2(20)
)
;
create sequence auto_seq;
create trigger aut_bri
before insert on auto_numb
for each row
begin
select auto_seq.nextval into :new.n1 from dual;
end;
/
rem
rem Note that the trigger ALWAYS fires
rem
insert into auto_numb (v1) values ('asdf');
insert into auto_numb values (88,'qwerty');
select * from auto_numb;
N1 V1
--------- --------------------
1 asdf
2 qwerty
This may not solve all your problems, of course.
If you are planning to use the primary key from this table as a foreign key to another table, how do you get the value back to your application ? One option, of course is to forget about the trigger and simply select the value into a local variable. Another is to use a new feature of Oracle 8, the RETURNING clause, possibly eliminating one reound-trip between the client and server. For example, in SQL*Plus:
Using the RETURNING clause in SQL*Plus
variable m_ret number
insert into auto_numb (v1) values ('asdf')
returning n1 into :m_ret;
print m_ret
M_RET
---------
3
One thing you should definitely NOT do is to create a trigger on the child table and try to use the CURRVAL feature of sequences to populate the foreign key column. It may seem cute and quick at first sight as a way of getting a parent and its children into the database in a single group, but since the trigger always fires what do you do when you want to insert further child rows some time later ?
Note: Only the owner of the table needs to be able to see the sequence.
Note: Watch out for anything that may disable the trigger.