Re: What's the equivalent of "identity"(ms sql) in oracle?

From: Christopher Beck <clbeck_at_us.oracle.com>
Date: Tue, 08 Dec 1998 17:23:58 GMT
Message-ID: <36715d61.14228589@inet16.us.oracle.com>

On Tue, 08 Dec 1998 17:03:45 GMT, Pauline Ohanian <pauline_at_isol.com.lb> wrote:

>hi,
>I want to know how to make a field in a table an autoincrement field.
>With MS SQL, you just define the field as "identity" and whenever you
>insert a record,
>you don't insert any value for this field and automatically this field
>will get the value of the
>last record field value + 1. How can I do this in Oracle?
>

You can use a database sequence and a trigger.

eg.

create sequence my_seq;

create or replace
trigger bi_fer_my_table
before insert on my_table
for each row
begin
  select my_seq.nextval
    into :new.col1
    from dual;
end;
/

• NOTE *

This WILL NOT guarantee 'no gaps' ( ie. all values for 1 .. n will be in the table ), but it will guarantee uniqueness.

hope this helps.

chris.

>
>Thanks in advance,
>Pauline

--
Christopher Beck
Oracle Corporation
clbeck_at_us.oracle.com
Reston, VA.

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Tue Dec 08 1998 - 11:23:58 CST