Re: sequences

Close,
INSERT INTO employee( empno, ename )
VALUES ( employee_empno_seq.NEXTVAL, 'GREEN');

>>> "Natasha Batson" <nbatson_at_neal-and-massy.com> 07/12/00 03:01PM >>>
Hi Glen

Just some basic info.

With regards to the use of sequences which are actually separate Oracle = objects like
tables and indexes, they are used in INSERT statements along with the = pseudocolumn
NEXTVAL which generates a new sequence number or CURRVAL which says which = is
the last sequence value used.

e.g. To create a sequence that will start with the value of 1000 and = increment by 1
such that the sequence is 1000, 1001, 1002, etc. this could be the SQL = statement used
( there are other options which you could check in your SQL Reference = manual or=20
Oracle CD documentation ) :

CREATE SEQUENCE employee_empno_seq
START WITH 1000
INCREMENT BY 1
NOCACHE;=20
( by default Oracle will generate 20 sequence values and place them in the = cache for
future use by users )

The sequence could then be referenced in an INSERT statement using NEXTVAL = e.g:
INSERT INTO employee( empno, name )
VALUES (employee_empno.NEXTVAL, 'SMITH');

And this would generate the next value in the sequence and insert it into = the empno column
of the employee table. Since we didn't use the sequence yet, employee_empno= .NEXTVAL
would generate the value 1000. And if we were to reference the sequence = again in another
INSERT statement e.g.

INSERT INTO employee( empno, ename )
VALUES ( employee_empno.NEXTVAL, 'GREEN');

A row with the values 1001 for the empno and GREEN for the ename would now = be inserted.
So each time NEXTVAL is referenced it generates a new unique value for the = sequence. Follow?

Hope this info helped : )

Regards
Natasha

• Original Message -----=20 From: Glenn Travis=20 To: Multiple recipients of list ORACLE-L=20 Sent: Wednesday, July 12, 2000 1:23 PM Subject: sequences

  I wanted to ask how people are handling the insertion of unique keys in = Oracle. Please understand that my experience with generating unique keys = comes from an Informix background, wherein you can have a column with = datatype of 'serial'. This is an integer column which gets generated at = insert time with the next value automatically. You can then check the = sqlca area (return buffer) for the inserted value.   =20
  I know in Oracle you can accomplish this with the SEQUENCE function. As = this is not automatically inserted by Oracle, how is the unqiue key value = inserted? Do most people use a before trigger, stored procs, or call the = sequence themselves? If using a client program (c, java), how is the = value for the unique key returned to the program which performed the = Received on Wed Jul 12 2000 - 14:29:38 CDT