Re: nextval from dual;

Richard Kuhler wrote:

> damorgan wrote:
> >
> > 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

>

I stand corrected on the last point but it is a means to the end ... however fragile.

Daniel Morgan Received on Wed Nov 27 2002 - 12:53:46 CST