Re: NextVal quirk

On Fri, 18 Sep 1998 14:27:43 -0500, Patrick Janidlo <janidlo_at_visionarysoftware.spamthis.com> wrote:
>On WindowsNT 4.0 Server service pack 3, Oracle 8.0.4.0.0,
>using the SQL*Plus tool 8.0.4.0.0
>
>I have a sequence (FieldID) defined as incrementing by 1.
>
>When I insert for example:
>
>insert into tablex
> (field_id, field_name)
>values
> (fieldID.NextVal,'test');
>
>where FieldID is a sequence, the nextval command increments the value by
>two, thus the first number is 2, the next is 4, etc.
>
>If I use the CurrVal, I get the error:
>
>ERROR at line 1:
>ORA-08002: sequence FIELDID.CURRVAL is not yet defined in this session
>
>the statement:
> select * from user_sequences where sequence_name = 'TABLEX';
>
>gets
>
>SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY C O
>CACHE_SIZE LAST_NUMBER
>------------------------------ --------- --------- ------------ - - --------
>-- -----------
>TABLEX 1 1.000E+27 1 N N
>20 21
>
>Any help is welcomed.
>

RTFM. The SQL Reference notes that you must first initialize the sequence with NEXTVAL. Oracle increments it once for how ever many times you use the two vals in a statement, so either you are not incrementing by one or you have another statement in your transaction incrementing NEXTVAL.

>
>

jg

-- 
These opinions are my own and not necessarily those of Information Quest or 
Pebble In The Sky http://www.informationquest.com mailto:jgarry_at_nospameiq.com
http://ourworld.compuserve.com/homepages/joel_garry   Remove nospam to reply.  
mailto:joel_garry_at_compuserve.nospam.com  "See your DBA?"  I AM the @#%*& DBA!