Re: sequences

Whoops! Sorry! Forgot what I called it : )

• Original Message ----- From: "William Beilstein" <BeilstWH_at_obg.com> To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Wednesday, July 12, 2000 4:45 PM Subject: 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
Oracle CD documentation ) :

CREATE SEQUENCE employee_empno_seq
START WITH 1000
INCREMENT BY 1
NOCACHE;
( 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 ----- From: Glenn Travis To: Multiple recipients of list ORACLE-L 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.

  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 insert?

  Examples, methods, design recommendations welcome...

--
Author: William Beilstein
  INET: BeilstWH_at_obg.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may