How to autoincrement a field in oracle?

Here is the link that I used as reference,

http://jen.fluxcapacitor.net/geek/autoincr.html
 CREATE TABLE “Database1”.”table1″
   ( “Id” NUMBER NOT NULL ENABLE,
 “Field2” VARCHAR2(50 BYTE),
 “Field3” DATE,
 “Field4” VARCHAR2(2000 BYTE),
 “Field5” VARCHAR2(50 BYTE),
  “Field6” VARCHAR2(4000 BYTE),
 “Field7” VARCHAR2(50 BYTE),
 “Field8” VARCHAR2(50 BYTE)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 32768 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE “BPS_DAT” ;

create sequence table1_seq
start with 1
increment by 1;

create or replace trigger table1_insert
before insert on table1
for each row
declare
    max_id number;
    cur_seq number;
begin
    if :new.Id is null then
        — No ID passed, get one from the sequence
        select table1_seq.nextval into :new.Id from dual;
    else
        — ID was set via insert, so update the sequence
        select greatest(nvl(max(Id),0), :new.Id) into max_id from table1;
        select table1_seq.nextval into cur_seq from dual;
        while cur_seq < max_id
        loop
            select table1_seq.nextval into cur_seq from dual;
        end loop;
    end if;
end;

Advertisements
This entry was posted in Oracle. Bookmark the permalink.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s