The most asked question about oracle from newbies

The most asked question that newbies ask me when starting using Oracle is:
How to create an "autonumber" column?

Unfortunately there is not way to directly create that (or to associate a sequence to a table for future reference).
You have to:



1. Create the sequence (I will use a naming convention that must be "adjusted" if the table name is bigger than 26 characters)

create sequence <table_name>_seq;


2. Create a row level trigger in the table

a)Oracle version lower than 11g:

CREATE OR REPLACE TRIGGER <tabela_name>_I
BEFORE
INSERT
ON <table_name>
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
begin
if :new.<id_column_name> is null then
select <table_name>_seq.nextval
into :new.<id_column_name> from dual;
end if;
END;


b)if Oracle version >= 11g (avoiding the "select from dual")

CREATE OR REPLACE TRIGGER <table_name>_I
BEFORE
INSERT
ON <table_name>
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
begin
if :new.<id_column_name> is null then
:new.<id_column_name>:=<table_name>_seq.nextval;
end if;
END;


or if your Oracle version supports conditional compilation (available in Oracle 10g Release 2 but back-ported to Oracle 10g Release 1 in 10.1.0.4.0)

CREATE OR REPLACE TRIGGER <tabela_name>_I
BEFORE
INSERT
ON <table_name>
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
begin
if :new.<id_column_name> is null then
$IF dbms_db_version.version>11 $THEN
:new.<id_column_name>:=<table_name>_seq.nextval;
$ELSE
select <table_name>_seq.nextval
into :new.<id_column_name> from dual;
$END
END IF;
END;



The trigger will make sure that when inserting a new row the id column will get a new sequence value.
note: In Oracle the sequence number is not assured that will be used without gaps or in increasing value

Sequence numbers are generated independently of tables, so the same sequence can be used for one or for multiple tables. It is possible that individual sequence numbers will appear to be skipped, because they were generated and used in a transaction that ultimately rolled back. Additionally, a single user may not realize that other users are drawing from the same sequence.

After a sequence is created, you can access its values in SQL statements with the CURRVAL pseudocolumn, which returns the current value of the sequence, or the NEXTVAL pseudocolumn, which increments the sequence and returns the new value.

Documentation

Beware that the previous statement is not 100% correct. If you try to use the CURRVAL pseudocolumn for instance like
select <sequence_name>.currval from dual
or if >11g

begin
dbms_output.put_line(<sequence_name>.currval);
end;

before using the sequence in the session (i.e using NEXTVAL pseudocolumn)
you will get an "ORA-08002: sequence <sequence_name>.CURRVAL is not yet defined in this session" error!
The solution is to use something like select last_number from user_sequences where sequence_name='<sequence_name>';

Good coding and happy holidays!


This post is a somewhat translation/update of two previous posts.

Este post é em parte uma tradução de dois posts anteriores em português