How to create Auto number functionality in Oracle

Some time we need to create MS Access like Auto number feature for Oracle tables. We can get the same functionality by using this script.

1) Create Table

CREATE TABLE activity (
activity_id NUMBER NOT NULL,
description VARCHAR2(50) NULL );


2) Create primary key constraint

ALTER TABLE activity ADD ( PRIMARY KEY (activity_id) ) ;

3) Create a sequence for table

CREATE SEQUENCE sq_activity;

4) Create before insert trigger on table

CREATE TRIGGER tbi_activity
BEFORE INSERT ON activity
FOR EACH ROW
BEGIN
IF :new.activity_id IS NULL THEN
SELECT sq_activity.nextval
INTO :new.activity_id
FROM dual;
END IF;
END;
/


5) Insert new record into activity table

Insert into activity (activity_id,description) values(null,'Test');
Insert into activity (activity_id,description) values(null,'Test 2');

6) Commit;

7) Select * from activity

8) if you have more than one tables you may use following statements to create sequences and triggers on tables.

SELECT 'create sequence sq_' || table_name || ' ;'
FROM user_tab_columns
WHERE column_id = 1 AND
data_type = 'NUMBER'
ORDER BY table_name
,column_id;




SELECT 'create trigger tbi_' || table_name ||
' before insert on ' || table_name ||
' for each row begin if :new.' || column_name ||
' is null then select sq_' || table_name ||
'.nextval into :new.' || column_name ||
' from dual; end if; end;' || chr(10) || '/'
FROM user_tab_columns
WHERE column_id = 1 AND
data_type = 'NUMBER'
ORDER BY table_name
,column_id;