Click here to Skip to main content
Click here to Skip to main content
Go to top

Oracle Sequence Promotes Poorly Maintainable Code

, 4 Apr 2013
Rate this:
Please Sign up or sign in to vote.
Oracle tables have no built-in mechanism for auto numbering. Instead, you must create a separate unique Sequence object and use it each time a record is inserted into the database.

I started my professional career working with Microsoft's SQL Server. I spent twelve years off and on learning how to design a database and write stored procedures in T-SQL. Then this last October, I switched jobs and was exposed to a new database platform, Oracle. Since this switch, I have used every curse word I know and invented new ones to express my frustration at interacting with an Oracle 10x something database. I'll leave the rest of my ranting for another post and just focus on one aspect of Oracle that has frustrated me recently.

I started creating my first new table in Oracle and started defining the columns. I always start with an ID column that is typically used as primary key of the table. As I went to select the column type, I didn't see anything labeled "autonumber". Trying again, I looked for integer, but that isn't there either. Oracle only supports the "Number" column. There, you can provide the precision before and after the decimal point. After selecting the number column, I looked all over for something that would mark the column as unique and set for an autonumber sequence. Striking out  quickly, it was time to ask Google and start learning about Sequence objects.

Oracle tables have no built-in mechanism for auto numbering. Instead, you must create a separate unique Sequence object and use it each time a record is inserted into the database.

CREATE SEQUENCE customers_seq  START WITH     1000  INCREMENT BY   1  NOCACHE  NOCYCLE;

Then each time the sequence is used, it looks something like

INSERT INTO customers (ID, Name...) VALUES (customers_seq.nextval, 'Hogan Haake'...);

Comparing this to the SQL Server I'm used to, if a column is autonumbered, you just exclude it in the insert and it automatically gets the next ID on insert.

INSERT INTO customers(Name, ...) VALUES ('Hogan Haake'...)

At this point, Oracle people could argue that I'm just lazy, or I just need to learn a new way. They are right on both accounts, but there is more to the story! I recently came across some bad code in part of my application where the developer didn't use the sequence.nextvalue for an insert, instead converting the current date into a number [YYMMDD Format] and inserted that into the table as a unique value. While that method worked, the unique number they were generating was quite far away from the current sequence. The system has been in production for two years now and the sequence number is about 6 months away from a "collision" with incorrectly inserted manual numbers in the ID column.

Current Sequence Value      Manual Sequence Value
107,000                     120,210     (first inserted 2012-Feb-10th)

The current sequence value is fast approaching the first manual sequence value. It was fortunate that the bug was found before it caused corrupt data and long nights for me. Due to the complexity of the system and time constraints, the simple fix of  incrementing the next value of the sequence to 500,000 to avoid any future collisions with "unique" numbers was chosen. It would be nice to fix the offending code with the correct sequence number, but management decided the code worked enough that we could move on to other problems.

In a SQL Server environment, if you try to insert a value into an autonumber field, an error is produced preventing this type of error from happening.

I'm not sure what other issues I'm going to encounter with this new environment, but I sure miss SQL Server. If you still don't think SQL Server is better, consider community support. Who would you rather trust for help?

Pinal Dave (SQL Server) or Don Burleson (Oracle)?

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

snorkie
Software Developer (Senior)
United States United States
I'm a nice guy Smile | :)

Comments and Discussions

 
QuestionSurrogate Keys PinmemberJasmine25018-Apr-13 10:00 
AnswerRe: Surrogate Keys Pinmembersnorkie8-Apr-13 11:41 
GeneralRe: Surrogate Keys PinmemberJasmine25018-Apr-13 13:12 
GeneralRe: Surrogate Keys Pinmembersnorkie9-Apr-13 3:03 
GeneralRe: Surrogate Keys PinmemberJasmine25019-Apr-13 6:09 
QuestionUse Trigger PinmemberKlaus Luedenscheidt4-Apr-13 19:43 
AnswerRe: Use Trigger Pinmembersnorkie5-Apr-13 6:04 
GeneralRe: Use Trigger PinmemberKlaus Luedenscheidt5-Apr-13 17:48 
QuestionQuestion PinmvpMika Wendelius4-Apr-13 6:53 
Hi,
 
While I understand your point that using a sequence may feel clumsy I don't quite understand why sequences promote poor code?
 
Wasn't the example in your blog a situation where a developer didn't follow system design and implementation guidelines? I'm not saying that it was deliberate, perhaps it was because lack of knowledge, false instructions or something else, but anyhow if the original idea was to use sequences...
 
You're right that sequences are separated from tables and they have to be used in a different way. However sequences can be used in several kinds of scenarios, not just providing numbers for surrogate keys. One thing to keep in mind is that they also can offer a system wide uniqueness etc.
 
If a behaviour similar to autoincrement field is required, why not simply use a trigger to do the task? For example: Mimicking SQL Server identity column in Oracle[^]
GeneralRe: Question Pinmembersnorkie4-Apr-13 7:19 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web01 | 2.8.140916.1 | Last Updated 4 Apr 2013
Article Copyright 2013 by snorkie
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid