Oracle Sequence Promotes Poorly Maintainable Code

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