sequence.nextval question

Note: using Oracle 7.3.4. Don't know what version of OAS.

I'm using an MS-Access front end (to create entry forms and reports) which will insert data to an Oracle table. In the Oracle table there will be a field named ID which will hold a unique numeric incremental value. So, I've created a sequence for this and through an http request via OAS (since I don't know how to get a sequence.nextval from Access directly), I will request the nextval. The problem is that I can't seem to guanantee that two users entering something concurrently will not ever end up getting the same ID.

Here's the scenario I'm thinking of:
Person1 calls the sequence.nextval which returns the number 1000. Person2 calls the sequence.nextval which returns the number 1001. Person1 gets the max(ID) which will be 1001. Person2 gets the max(ID) which will also be 1001.

The problem is that in PL/SQL I can't seem to put sequence.nextval into a variable directly, but can only stick it in part of an INSERT statement.

Is there a way that I can directly return the value created by sequence.nextval? Like I said, I'd love to be able to just declare a variable and have it equal to sequence.nextval, but that doesn't seem possible. And if there isn't a way, I wonder why Oracle would restrict it. It's a sequence, only a sequence, you should be able to do anything with it you want to.

Thanks, in advance,
Mark

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Sun Aug 20 2000 - 02:35:20 CDT