472,328 Members | 1,596 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,328 software developers and data experts.

Sequence.nextval incrementing itself over time

My Oracle sequences seem to be auto-incrementing themselves "over time".
My row ID's are sequenced like this:

Expand|Select|Wrap|Line Numbers
  1. 1, 4, 5, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 41, etc
I'm using "sequencename.nextval" when inserting new rows into my tables, so one would think they'd come in a straight sequence?


Expand|Select|Wrap|Line Numbers
  1. INSERT INTO hprequests (requestid, username, flagtype, reqstatus, workstatus, requesttitle, statuschangedby)
  2. VALUES(seqrequestid.nextval, 'username', 'flagtype', 'reqstatus', 'workstatus', 'reqtitle', 'statuschangedby')
They seem to be increasing properly when I insert several rows in a row (no pun intended), but if I wait say, a day or two, before inserting another row - it may have been increased by 10 or what not.

I'm using Oracle 9 and OleDB objects in C# to execute the queries.

Have anyone experienced anything like this?
Could it somehow be related to a query failing? Would "execution" of a query increase the value of the sequence despite the failure of the query?
Jul 31 '08 #1
8 11036
13,262 8TB
If your id is set to auto increment then why are you providing it in your insert statements? You are trying to increment the id's yourself while the database is also trying to do the same thing for you?
Jul 31 '08 #2
I probably shouldn't have used the word "auto-increment". As far as I know a field can't be set to auto-increment in Oracle (as it can with e.g. MySQL). That's why I need the sequence to increment for me.

I'll try to rephrase myself:
What I ment was that the sequence seems to be incrementing by itself. Even if my application isn't used. So the next time the application is used, and a row is inserted, the sequence does not provide the correct "nextval" but rather a too high value.

I tried to run bad queries from the application on purpose, making them fail, but that didn't provoke the sequence increase either. It seems to be related to time somehow...
Jul 31 '08 #3
13,262 8TB
What's your sequence definition like? Do you have a trigger on the table for the sequence?
Jul 31 '08 #4
My sequence was defined like this:

Expand|Select|Wrap|Line Numbers
  1. create sequence SEQREQUESTID
  2. increment by 1
  3. start with 1;

I don't have any trigger on the table for the sequence (as far as I'm aware of...?)
I'm not sure I quite followed you on that one?
Did you mean if there is any reference between the sequence and the table, or the ID field? The answer to that would be no. Should there be? Is there some way to tell the ID field to use that particular sequence rather than using seqrequestid.nextval?
Jul 31 '08 #5
I just noticed something rather strange:

It seems to be skipping to "n1" (not without exceptions though...)
So, it has skipped to 21, 41, 61 and 81 so far.
Jul 31 '08 #6
8,127 Expert 4TB
in the sequence defination use the NO CACHE key word.

because if you restart the server all the values in the cache are lost.
Jul 31 '08 #7
153 100+
also any failed attempts to insert will lose the grabbed sequence value. all the sequence truly guarentees is a unique value. several conditions can cause "skips" in the numbers.
Aug 1 '08 #8
Debasisdas is on to something. I looked up the no cache thing and found the following which should explain the issue:

When used in a non-cache mode, an access of a sequence which requests
the 'nextval' will increase the current value by the number specified
in the 'increment' section of the sequence and return the new value.
As an example, for a sequence with a current value of zero, an
increment of one, and no cache, the current value would become one,
and one would be returned to the calling statement.

When used with a cache, an access of the sequence which requests the
'nextval' will increase the current value by the increment times the
cache, and will return to the calling function the current value plus
one times the increment. The following access of the nextval *should*
access the cached seqence values and return a number which equals the
previous plus the increment. Ideally, numbers will continue to be
returned from the cache until it has reached the end.

Taken from this article.

As I also needed to display my ID's, I found another way that also seems to have solved the skipping issue. I select the nextval before inserting:

Expand|Select|Wrap|Line Numbers
  1. SELECT seqrequestid.nextval FROM DUAL
I have done some testing on this and I haven't had any skips since I switched to this method.
Thanks for your input everyone!
Aug 4 '08 #9

Sign in to post your reply or Sign up for a free account.

Similar topics

by: David Stockwell | last post by:
I'm sure this has been asked before, but I wasn't able to find it. First off I know u can't change a tuple but if I wanted to increment a time...
by: DFS | last post by:
FYI, Using DLookup("ResultsField","Pass-thru query") consumes 2 SEQUENCE numbers each time it's called. Anyone know why?
by: dan | last post by:
I have 2 tables, tab1 ( integer incremented sequence , col2, col3 ) and tab2 ( integer from tab1, col4, col5 ). When I call this function to add a...
by: Oscar Tuscon | last post by:
I'm looking at ways to get batches of sequence values s faster. I don't want to set cache or increment to a large number for a variety of reasons. I...
by: urban.widmark | last post by:
Hello We are having some problems with triggers, sequences and union all in V8 on code that worked fine in V7. Was wondering if someone else has...
by: alasdair.johnson | last post by:
A bug I noticed which doesn't seem to be posted on usenet, nor recognized by Oracle (who didn't want to help unless we had been using their ADO.NET...
by: priyan | last post by:
hai everyone, I am having a doubt in inserting data in time field. I am having a table in which in column in timestamp without time zone...
by: Rahul B | last post by:
Hi, If i do a "Select nextval for <seq_namefrom sysibm.sysdummy1", it increases the values of nextval by1. How can i find out the nextval of a...
by: Haneef | last post by:
Hi All The problem is that I need to keep track of time in my program. Getting time using system calls every time I need is definitely very...
by: tammygombez | last post by:
Hey fellow JavaFX developers, I'm currently working on a project that involves using a ComboBox in JavaFX, and I've run into a bit of an issue....
by: concettolabs | last post by:
In today's business world, businesses are increasingly turning to PowerApps to develop custom business applications. PowerApps is a powerful tool...
by: better678 | last post by:
Question: Discuss your understanding of the Java platform. Is the statement "Java is interpreted" correct? Answer: Java is an object-oriented...
by: teenabhardwaj | last post by:
How would one discover a valid source for learning news, comfort, and help for engineering designs? Covering through piles of books takes a lot of...
by: CD Tom | last post by:
This happens in runtime 2013 and 2016. When a report is run and then closed a toolbar shows up and the only way to get it to go away is to right...
by: CD Tom | last post by:
This only shows up in access runtime. When a user select a report from my report menu when they close the report they get a menu I've called Add-ins...
by: antdb | last post by:
Ⅰ. Advantage of AntDB: hyper-convergence + streaming processing engine In the overall architecture, a new "hyper-convergence" concept was...
by: Matthew3360 | last post by:
Hi there. I have been struggling to find out how to use a variable as my location in my header redirect function. Here is my code. ...
by: Matthew3360 | last post by:
Hi, I have a python app that i want to be able to get variables from a php page on my webserver. My python app is on my computer. How would I make it...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.