Power to Build

Home » Databases » Oracle » Gotcha: Oracle Sequences – Contd…

Gotcha: Oracle Sequences – Contd…

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??

wrong_way_to_use_Oracle_sequence

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.


Comments, please?

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: