After pointing out how not to create auto increment columns in Oracle, I suppose I should point out how to create auto increment columns in oracle.
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_sequence
START WITH 1
INCREMENT BY 1;
Sequence created.
Now we can use that sequence in an BEFORE INSERT trigger on the table.
CREATE OR REPLACE TRIGGER test_trigger
BEFORE INSERT
ON test
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
SELECT test_sequence.nextval INTO :NEW.ID FROM dual;
END;
/
Trigger created.
This trigger will automatically grab the next value from the sequence we just created and substitute it into the ID column before the insert is completed.
Now we’ll do some inserts:
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 Jon
2 Bork
3 Matt
Because the sequence is updated independent of the rows being committed there will be no conflict if multiple users are inserting into the same table simultaneously.
If you need to capture the value of the auto increment column you’ll want to check out my other article Oracle Auto increment Columns – Part 2
Matt has posted a similar method in which he uses a sequence in the insert eliminating the need for the trigger. That will work just as well without the need for the trigger. The only drawback to this method is slightly longer SQL commands.
in the trigger code whats the meaning of that “dual” word
this code works perfectly right up until i delete a row from the table and start adding new rows.. the sequence starts jumping numbers.. help! any suggestions would be appreciated!
this code is just i’ve searched for, thanks.
I need help in getting the generated value using java:
CREATE TABLE person (ID NUMBER(11) NOT NULL, givenName NVARCHAR2(60) default ‘A default value’ NOT NULL, surName NVARCHAR2(60) NOT NULL, CONSTRAINT person_pk PRIMARY KEY ( ID ) ) ORGANIZATION INDEX;
String sql = "insert into person (givenName,surName) values (?,?)";
PreparedStatement st = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
st.setString(1,"me");
st.setString(2,"fromMars");
st.executeUpdate();
ResultSet rs = st.getGeneratedKeys();
if (rs.next()) {
Object obj = rs.getObject();
}
The returned Object is a ROWID, and i’m not able to get the ID for the inserted row.
… found a working solution:
String generatedColumns[] = {"ID"};
PreparedStatement st = conn.prepareStatement(sql, generatedColumns);
…
if (st.executeUpdate() != 0)
{
ResultSet rs = st.getGeneratedKeys();
if (rs.next())
int idValue = rs.getInt(1);
}
This is fantastic. Thanks for making it so simple!
I have this table :
CREATE TABLE PARTY_INFORMATION (
PARTY_CODE VARCHAR2 (10) NOT NULL,
PARTY_NAME VARCHAR2 (100) NOT NULL,
ADDRESS VARCHAR2 (200),
PHONE VARCHAR2 (50),
FAX VARCHAR2 (50),
EMAIL VARCHAR2 (100),
WEB VARCHAR2 (100),
CONTACT_PERSON VARCHAR2 (100),
REMARKS VARCHAR2 (200),
CONSTRAINT CONS_PARTY_NAME
UNIQUE (PARTY_NAME),
PRIMARY KEY ( PARTY_CODE ) ) ;
Here PARTY_CODE format is P1001 …P1010 etc…
i want to increment the party_code automatically while insert new row.
please help me.
thanks in advance.
Hi,
i need to create autogenerate column after inserting data. That is after insert trigger. Pls send the sample code
thans for d` great tutorial….
Thank you dear….really very informative…
What if i want to generate nos. in these sequences
10-01
10-02
10-03
10-04…. where 10 is year and 01,02,03,04 etc are nos.
if year changes from 10 to 11 (i.e. 2010 to 2011) nos. also changes i.e.
11-01
11-02
11-03
11-04
Please help me out in this
Thank you very much in advance
i have face a problem that is how the user will get autogenerated id number on this form which should be shown in a label or list_item after submission of some of his personnel data like name,sex, city,adddress,fax no, etc…….through the front end i.e oracle form….
pl sir give full details about my problem…..
waiting for your favourable reply……..
i have created sample, sequence and trigger. but when i tried to insert values exactly like you above i got error.
ORA-01400: cannot insert NULL into (“KRANTHI”.”SIMPLE”.”ID”)
what could be the problem
thanks alot for this solution.
I tried on many sites to find out the exact solution but no body gave answer related to “oracle xe”. so once again thanks….
Gautam jangra
noida sec 62
Auto increment of the column in 11G without any trigger only applicable in 11g
CREATE SEQUENCE “CM_4_0″.”TEST_SEQ” MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 2 START WITH 1000031624 CACHE 20 NOORDER NOCYCLE ;
create or replace function F return number as
a number;
begin return
select TEST_SEQ.NEXTVAL into a from DUAL;
return a;
end;
drop table Z
create table Z( name varchar2(10), PRI number generated always as(f()) VIRTUAL );
insert into z(name)values(‘asasa’);
insert into Z(name)values(‘asasa’);
insert into z(name)values(‘asasa’);
insert into z(name)values(‘asasa’);
commit;
create sequence TEST_SEQ minvalue 1 maxvalue 9999999999999999999
INCREMENT BY 2 START WITH 1000031624 CACHE 20 NOORDER NOCYCLE ;
create or replace function F return number deterministic as
a number;
begin
select TEST_SEQ.NEXTVAL into a from DUAL;
return a;
end;
drop table Z
create table Z( name varchar2(10), PRI number generated always as(f()) VIRTUAL );
insert into Z(name)values(‘asasa’);
insert into Z(name)values(‘asasa’);
insert into z(name)values(‘asasa’);
insert into z(name)values(‘asasa’);
create or replace function F return number as
a number;
begin
select TEST_SEQ.NEXTVAL into a from DUAL;
return a;
end;
select * from z
insert into Z(name)values(‘asasa’);
insert into Z(name)values(‘asasa’);
insert into Z(name)values(‘asasa’);
insert into z(name)values(‘asasa’);
commit;
create or replace trigger tbl_Missing_trigger
before insert
on test
referencing new as new
for each row
begin
select tbl_MissingStatus_sequence.nextval into :new.id from dual;
end;
WHEN I RUN THIS COMMAND LINES
A BOX FOR THE VARIABLES APPEARS
TO GIVE THE NAME, TYPE AND THE VALUE
AND EVEN AFTER I GIVE THESE CONSTRAINS AN ERROR FOLLOWS AS
ORA-01036: illegal variable name/number
WHAT SHALL I DO???
I have the table sequence and trigger as mentioned in the example in this post.
Now i have to rename the existing table.
I did rename table and rename sequence after this reference of the table name in trigger did change automatically but it did not change the sequence name and now it is giving me error saying:
ORA-04098: trigger ‘orcal.TEST_PKT’ is invalid and failed re-validation
Any pointers on this?
Awesoome..GREAT POST..