Re: Sequence problem

On 7 Mar 1997 18:20:56 GMT, dpcauh_at_engdev2.sannet.gov (Jack Hurlbut) wrote:

> I have a sequence called ETYPE_KEY that was created with default values.
>When I try to use it in an insert it fails. The screen grab
>from SQLPLUS can explain the best:
>
>SQL> select ETYPE_KEY.currval from dual;
> CURRVAL
>----------
> 43
>SQL> insert into EAS_TYPE (EAS_TYPE,STATUS,REMARK,
> 2 ETYPE_KEY,
> 3 ED_KEY,
> 4 POST_DATE,USER_ID) values ( 'AV', 'ABX', NULL,
> 5 ETYPE_KEY.NextVal,
> 6 970306135247 , sysdate, 'DPCAUH') ;
>ETYPE_KEY.NextVal,
>*
>ERROR at line 5:
>ORA-01400: mandatory (NOT NULL) column is missing or NULL during insert
>
>SQL> select ETYPE_KEY.currval from dual;
> CURRVAL
>----------
> 44
>
>
>The sequence fails on the insert but the value of ETYPE_KEY.currval has
>increased by 1. I'm new at using sequences but it doesn't seem like
>rocket science. What am I missing?

Jack,

The value of a sequence is increased whenever you access the sequence via nextval. It does not matter if your transaction fails or not, the sequence is still incremented.

Shawn Mathews
SemTech_at_Mindspring.com Received on Sat Mar 08 1997 - 00:00:00 CST