Thursday 21 August 2014

How To Add Auto Increment In Oracle- IDENTITY column in Oracle 12c

Lets create employee table for this purpose.
SQL> CREATE TABLE EMP01
(
EMPID NUMBER,
NAME VARCHAR2(50),
DEPT VARCHAR2(20)
);

Next steps is to create oracle sequence to generated the id values.
SQL> CREATE SEQUENCE EMPID_SEQUENCE
START WITH 1
INCREMENT BY 1;

Next Step is to create Trigger to assign the values from sequence to EMPID column.

CREATE OR REPLACE TRIGGER EMPID_TRIGGER
BEFORE INSERT ON EMP01
FOR EACH ROW
BEGIN
SELECT EMPID_SEQUENCE.nextval INTO :NEW. EMPID  FROM dual;
END;
/
Now we will try insertng few values:

SQL> INSERT INTO EMP01 (NAME, DEPT) VALUES ('RON',’ABC’);
1 row created.
SQL> INSERT INTO EMP01 (NAME, DEPT) VALUES ('VICTORIA',’XYZ’);
SQL> SELECT * FROM EMP01;
EMPID NAME DEPT
---------- ------------------------------
1 RON ABC
2 VICTORIA XYZ


You can find that the EMPID getting incremented by 1.
Now there is a new feature available in on Oracle 12c version:IDENTITY column using which we can implement the same auto increment feature.

Related Posts Plugin for WordPress, Blogger...

ShareThis