Generating id using nextval sequence

Hi!
I have created a vb application with a oracle backend. To create a primary key 'customerid' i have used the nextval sequence.The problem is this that whenever I close the application and restart it and insert the 'customerid' the value inserted is not the value next to the last value i.e if last value before closing was 13 then after restarting the values inserted will be inorder 21,22...
Can any one tell me what can be the problem?
Thankz

Check with your sequence creation. If 'cache n' is specified then oracle will try to generate the 'n' sequences well in advance and put in the cache so that requests can be served immediately. These cached sequences will be lost on the server restart.

This may be the reason. Do let me know if there is something else with it ..
sagar

Hi!i am sorry for replying late as i was busy with my project submission.
actually, this is what i have done
create sequence CustomerIDSequence
Start with 1
Increment by 1
NoMinValue
NoMaxValue
NoCycle
NoCache
Is there a mistake somewhere?as u c there is 'nocache' specified.is it because of 'nocycle'

Hi!i am sorry for replying late as i was busy with my project submission.
actually, this is what i have done
create sequence CustomerIDSequence
Start with 1
Increment by 1
NoMinValue
NoMaxValue
NoCycle
NoCache
Is there a mistake somewhere?as u c there is 'nocache' specified.is it because of 'nocycle'?

I had this problem with Oracle forms. Since a form's default status is NEW, the ID auto-incremented even though nothing was being done. I don't know about VB, but consider using a form-level procedure to create the ID based on a specific user action.

hmmmmm.i'm already using a procedure in oracle to call the sequence.it has 2 be something else

I've also had this issue when generating invoice #'s, when the data was rolled back it would cause a jump in the invoice#'s. Is it possible that a rollback is causing your issue?

A nextval on a sequence is not rolled back so that could cause jumps in your numbers if the inserts are rolled back.

FWIW - we have a gigantic Oracle DB with sequences the in the 15 digit range.
We don't worry if a number is missing. Our customer numbers are 10 digits, and we are missing soem numbers. Again, it is not a problem.

If sequential numbers are an absolute requirement, with no missing values, you will have to maintain a table that has one row for each customer number - pretty much like what the forms response (RodP) is saying above - roll your own. You cannot use a sequence.

you can also use query instead of Sequence like:
Sequence is used as
insert into table(id) values(my_seq.nextval);

but you can do this
Select max(id)+1 into v_seq from table;
insert into table(id) values(v_id);

Query works great as compare to sequence because sequence can generate Gaps but query never.

Hope that will help you.

In general it not matter if there are gaps in the sequence or not. If your application relies on that, then rethink your design. An artificial primary key (that's what a sequence number is) should never carry any further information.

Btw: if you are using the max() approach you should lock the table to avoid problems with parallel inserts or use another mechanism that ensures that two processes will never use the same ID, which can happen with the solution you use.

i understand what u guys are saying.the problem is this that
the people for whom i am designing this increment the customer id sequentially eg. cr1,cr2...now if i don't use a sequence 2 increment n do it thru the form then i have 2 use .recordcount +1 .the problem occurs when i delete a id ie the next id generated would be be the no.of records +1. but the problem occurs when v delete the last id then the id generated is the same as before but that id should not now be repeated.....so now what else can be done