Re: Auto/Incrementing number?? Beginner needs help!

You can create a sequence with a command of the form.

CREATE SEQUENCE MySequenceName;

in SQL*PLus.

To get values out of it you use the NEXTVAL operator of the sequence. So, when inserting a record into a table and you want a unique value for you primary key you might

INSERT INTO MyTable
( PKcol, col1, col2)
values
(MySequenceName.nextval, x, x);

This is the sort of thing you might want to consider doing inside a database trigger though.

NEXTVAL on a sequence gives (as its name implies I hope) the next value off the defined sequence. If you use it in an insert statement and then rollback the insert statemetn then, as far as the sequence is concerend the value has still be used and will not come up again (unless the sequence loops but I'm not getting into that here).

CURRVAl will give you the seuqnece's current value. You can only use this though, if you;ve already done a nextval in the current session.

HTH --
Alan D. Mills

Dan Gibbons wrote in message <73u99s$efk1_at_pound.sebank.se>...
>Hi,
>
>I have just been given an Oracle database (of which I know absolutly
nothing
>about) and have a really simple task which I just can't do! All I need to
>do is create a table with an auto incrementing number field like in SQL or
>Access (which is very is to do!).
>
>I have an Oracle users guide which mentions using a sequence. But where do
>I use a sequence? And how do I insert a new record into the table with it's
>unique auto number?
>
>If someone could please tell me the anwser to this most simple question
then
>I would be most greatful.
>
>Thanks
>
>Dan
>
>
>
>
Received on Mon Nov 30 1998 - 10:22:06 CST