Re: Sequence problem

You're not missing anything: that's just the way it works. Remember that others may be grabbing sequence numbers at the same time as you, so once a number is allocated, even if the statement fails, it cannot be re-allocated.

Chrysalis.

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?
Received on Fri Mar 07 1997 - 00:00:00 CST