The example contained in this article shows how to generate a sequence number for use as an entry in a table column when using Oracle. The sequence object is used to maintain a sequence number. This object can then be inserted into a table using the nextval function. You will likely need a separate sequence object for each table requiring a sequence number.

drop table kid;
drop sequence kid_seq;

create table kid
(
  id   decimal(3),
  name char(10) 
) ;

create sequence kid_seq
  minvalue 1
  start with 1
  increment by 1
  nocache;

-- table names are always stored as uppercase.
-- last_number is really the next number to be used.
select last_number - 1 from user_sequences
  where sequence_name = 'KID_SEQ';

insert into kid (id, name) values
  (kid_seq.nextval, 'William');

insert into kid (id, name) values
  (kid_seq.nextval, 'Cora');

select last_number - 1 from user_sequences
  where sequence_name = 'KID_SEQ';

insert into kid (id, name) values
  (kid_seq.nextval, 'Hannah');
  
insert into kid (id, name) values
  (kid_seq.nextval, 'Emma');

select * from kid;

select last_number - 1 from user_sequences
  where sequence_name = 'KID_SEQ';  

Here is the result of the last two instructions of this script (using Oracle 10g Express Edition).

In addition to the nextval function, there is also a currval function which can be used to retrieve the value of the most recent insert. This function is often used when you want to insert rows with the same number into another table.

We hope you will consider Caliber Data Training when you are in need of high quality database training including SQL.

Go to the articles index. Written by Bill Qualls. Copyright © 2007 by Caliber Data Training 800.938.1222