SQLPlus AUTO_INCREMENT Error

Many will gripe about this not being a standard feature in Oracle, but when it’s as easy as two more commands after your CREATE TABLE command I can’t see any good reason to use fancy SQL on every insert.First let’s create a simple table to play with.

SQL> CREATE TABLE test(id NUMBER PRIMARY KEY,name VARCHAR2(30));Table created.

Now we’ll assume we want ID to be an auto increment field. First we need a sequence to grab values from.

SQL> CREATE SEQUENCE test_sequenceSTART WITH 1INCREMENT BY 1;Sequence created.

Now we can use that sequence in a BEFORE INSERT trigger on the table.

CREATE OR REPLACE TRIGGER test_triggerBEFORE INSERTON testREFERENCING NEW AS NEWFOR EACH ROWBEGINSELECT test_sequence.nextval INTO :NEW.ID FROM dual;END;/SQL> INSERT INTO test (name) VALUES ('Jon');1 row created.SQL> INSERT INTO test (name) VALUES (’Bork’);1 row created.SQL> INSERT INTO test (name) VALUES (’Matt’);1 row created.SQL> SELECT * FROM test;ID NAME———- ——————————1 Jon2 Bork3 Matt

Oracle has no auto_increment, you need to use sequences.

Or - starting with Oracle 12.1 - you can simply have:

CREATE TABLE employee (    id NUMBER GENERATED by default on null as IDENTITY    ....)