Re: nextval from dual;

Richard Kuhler wrote:

> By jove I know you're wrong!
>
> CURRVAL only returns the last value retrieved from a NEXTVAL within your
> specific session. You can't use it at all if you haven't made a call to
> NEXTVAL. Regardless, it wouldn't give you the NEXT value like the
> poster asked for. I know of no easy way to get the next value without
> actually incrementing it. Perhaps there is some undocumented method to
> access the internal Oracle constructs but I'm not familiar with what it
> would be. More significantly, I can't imagine what use it would be to
> know what the next value is going to be.
>
> Just what are you going to do with this?
>
> No offense,
> Richard
>
> scotty wrote:
> >
> > Bag o' Fog wrote:
> >
> > >Could anyone tell me how to get the next value of an auto-number field
> > >in oracle without bumping the auto-number up one, like this seems to
> > >
> > >select autonum_sequence_corporate_id.nextval from dual;
> >
> > By jove I think i've got it!
> >
> > select autonum_sequence_corporate_id.currval from dual;

There is a very easy way to get the next value from a sequence without using NEXTVAL provided one is in a very low transaction environment. And in that case the solution will work 99% of the time but could always be incorrect on occassions. Try:

SELECT last_number + increment_by
FROM user_sequences
WHERE sequence_name = '....';

The liability is that someone might be using a number while you are checking and before you grab the next number yourself ... but that would exist with any scheme. Also one would have to avoid caching.

Daniel Morgan Received on Wed Nov 27 2002 - 10:25:30 CST