I just posted an error some developers got while inserting records into an Oracle table. I assumed there was an issue with Sequence used and replied back. I included that in my last post here.
I did receive a feedback after a week or 2. What I saw told me the developer did not have a clear understanding of the Sequence usage in Oracle. I will try to explain it here, so other developers may benefit from it.
As I mentioned in my original post about Oracle Sequences, it’s just a counter – sort of a global object, that is available anywhere within a SQL or PL/SQL script in Oracle. It’s an independent object by itself and to bump up the counter, you use NextVal function, which returns the new Value the counter is set to. You use this new value in your INSERT statement (why else would you get a new Sequence (counter) value, unless you are using to create a new record with the unique ID?). By using this method, you are keeping the Sequence and the last ID in the Table in sync.
Now, consider this. Some program uses this and another one uses Max(ID) + 1 approach. You can immediately that Sequence and the last ID in the table can fall out of synch right?
I heard user was inserting records in a batch (BULK?). Today, I asked to look at the program. As I guessed it, the developer was inserting records in a Loop (CFLOOP in Coldfusion).
Now, to get the ID, the developer used the Sequence once before the loop and set the value to a local variable. This variable is then incremented (correctly) inside the loop for each INSERT. Do you see how the Sequence has fallen way behind last ID used in the table??
See the problem there? The developer got NextVal from the Sequence correctly, but only at the top and loaded it to a local counter variable. After that, he incremented and used a local counter to load the table. At the end of the program,
the Sequence is w..a..y.. behind the actual last ID used in the table!!!!!! This, my friend, caused the Unique Constraint violation, next time the program ran. Since, this patter continues, the Sequence never catches with the last ID in the table and the program is forever banished to get this error!!
The key is to get NextVal every time you INSERT the Sequence value into the table. Here is a simple code that gets NextVal from a Sequence and inserts that into a table.
<cfquery name="getID" datasource="inquiries"> SELECT S_employee_id.NEXTVAL AS newID FROM dual </cfquery> .... ....<cfquery name="qrySubmitForm" datasource="inquiries"> INSERT INTO EMPLOYEE(employee_id) VALUES (#GetID.NewID#) </cfquery>
This defines a CF Query GetID. When used as shown the Query gets executed on each INSERT statement. This way, the Sequence and the last ID in table will be in synch. This will work, even when it goes into a Loop.