Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: NextVal quirk
On Fri, 18 Sep 1998 17:51:53 -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.
Is it possible that you have the before/after insert trigger on that table which selects from the same sequence? That would be the only explanation I could think of.
>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
Inside a session, you first have to get sequence's nextval to be able to refference its currval. So after you once reference the sequence by its nextval you should be able to reference it by its currval inside your insert statement. And if my assumption about an existing trigger is true, then the field_id values should increment by 1 when you use currval in your insert statements.
>the statement
>
>select * from user_sequences where sequence_name = 'TABLEX';
>
>SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY C O
>------------------------------ --------- --------- ------------ - -
>TABLEX 1 1.000E+27 1 N N
>
>CACHE_SIZE LAST_NUMBER
>---------- -----------
> 20 21
>
>
>Any help is welcomed.
HTH,
--
Jurij Modic <jmodic_at_src.si>
Certified Oracle7 DBA (OCP)