Re: Nextval Question

In article <23jtuj$mjh_at_snoopy.sra.com>, carmen_at_sra.com (Carmen Iannacone) writes:
>
> Hello Ora*Folks,
>
> I'm using Oracle's sequences to ensure uniqueness for key
> values in a table. In essence, I'd like the sequence to
> work like tickets at a deli counter. I'd like my Pro*C
> program to ask Oracle what the next sequence value is, pass
> it off to a user, and be sure that no two IDs will come back
> identical.
>
> My question is, the only way I can get the sequence's current
> or next value is by issuing a query which includes the pseudo-
> column name in it. i.e. (SQL*Plus example) :
>
> SQL> select ticket.nextval from deli;
>
> CURRVAL
> ----------
> 9
>
> This is fine as long as the "from" clause evaluates to a single
> row. Otherwise, it'll increment away, and return to my program
> only the last value (in essence "skipping" a bunch of otherwise
> allocatable numbers at the deli counter). Using currval above
> and incrementing it manually might seem OK on the surface, but
> concurrent accessors might both get the same currval, so callers
> should be asking for nextval...
>
> Do I have to create a special "one row" table and use that as the
> basis for the select? Are there other ways to access the pseudo-
> columns?
>

Oracle provides a one row table for occasions just like this. Surely you remember our good friend DUAL.

SELECT TICKET.NEXTVAL FROM DUAL;
>
> This may seem trivial (and I hope it is) but I can't seem to
> figure out the best way to proceed. Oops, one more disclaimer:
> Please note that I'm *not* a student, and this example has been
> sanitized for your convenience.
>
> Thanks for any responses, and I'll summarize to the group.
>
> Carmen
>
> (carmen_at_pni.sra.com)
>
>
> --
> ------------------------------------------------------------------------
> Carmen Iannacone (703) 803-1544
> Associate Member of the Professional Staff 4300 Fair Lakes Court
> SRA Corporation Suite 500
> Fairfax, Virginia Fairfax, Va. 22033
>

-- 
-----------------------------------------------------------------------------
    /\       Steven Champness (champs_at_cnb07v.hhcs.gov.au)
   /  \      Dept of Health, Housing, Local Govt and Community Services
  /    \     Brisbane, Queensland via Canberra, Australia
 /      \    
/   /    \   The opinions expressed above were forcibly implanted in my head
\  / /   /   by aliens from Alpha Zeltex 4, during a recent Invasion attempt.
 \/ / / /    
  \/ / /     MACINTOSH      : If you need a manual to operate it, it wasn't
   \/ /      SOFTWARE AXIOM : designed properly in the first place !!!!
    \/