Auto-increment in Oracle

I'm sure that 100s of references exist all over the web for this, but someone asked me today how to do this, and so it's trivial for me to add it onto this blog...

In MySql you have an 'auto-increment' column. Does Oracle have one, and if not, how do you implement one in Oracle?

Well, we tend to say it's best to wrap up your INSERT statements in procedures and then manually grab the sequence number yourself... but if not:

You might notice the rather useful RETURNING clause in the insert statements. This bit seems to be missing from most internet examples I found...

CREATE SEQUENCE rob_tmp_seq
/

CREATE TABLE rob_tmp_tab ( id NUMBER, descr VARCHAR2(200) )
/

ALTER TABLE rob_tmp_tab ADD CONSTRAINT rob_tmp_tab_pk PRIMARY KEY ( id )
/

CREATE TRIGGER rob_tmp_tab_trig BEFORE INSERT ON rob_tmp_tab FOR EACH ROW
DECLARE
BEGIN
  --
  IF :new.id IS NULL THEN
    SELECT rob_tmp_seq.NEXTVAL
    INTO   :new.id
    FROM   DUAL;
  END IF;    
  --
END;
/

SET SERVEROUTPUT ON SIZE 1000000

DECLARE
  --
  vn_number   NUMBER;
  --
BEGIN
  --
  INSERT INTO rob_tmp_tab( descr )
  VALUES                 ( 'This is a description' )
  RETURNING id INTO vn_number;
  --
  DBMS_OUTPUT.PUT_LINE( 'Created a record with the automatically assigned ID: ' || vn_number );
  --
  INSERT INTO rob_tmp_tab( id, descr )
  VALUES                 ( rob_tmp_seq.NEXTVAL, 'This is a description' )
  RETURNING id INTO vn_number;
  --
  DBMS_OUTPUT.PUT_LINE( 'Created a record with the ID grabbed from sequence manually ID: ' || vn_number );
  --
  INSERT INTO rob_tmp_tab( id, descr )
  VALUES                 ( 150, 'This is a description' )
  RETURNING id INTO vn_number;
  --
  DBMS_OUTPUT.PUT_LINE( 'Created a record with the ID specified manually ID: ' || vn_number );
  --
END;
/