Oracle's auto increment

The Oracle Database Management System doesn't have an auto-increment column type, we all knew that.

And some of the later Application development literature frown upon the idea of leaving the DB system to generate unique IDs for tupples in a database (tupples = rows in this case). The main reason is due to coupling of the Application and the DB system being used. There shouldn't be any coupling between the two, the story goes.

For example Entity Beans (EJBs that is) in J2EE has Container Management Persistence, which makes serialization of Object and Relational databases transparent - Ok, there are some complexities issues, since having all these flexiblity and transparency doesn't come cheap - It's a bit more complex to code and there's a performance hit, whatever minimal it is, it still exists. However, when and Entity EJB is coded properly to use CMP, it is a beatiful thing: The Enterprise Application Server does all of the SQL stuff and the developer only concentrates in solving business problems. No more SQL scripting.

We all know that the ideal ussage of a tool is not always the factual reality. Some session beans are still needed to do some ad-hoc database stuff, so we must still code SQL embeded in our Java classes (Hopefully using JDBC). So, the reality is that, RDBMS are still going to be used with newer OO Development techniques.

I will mention one way to create a unique id for a column type INTEGER for an Oracle database. Note that there are many different ways of doing this. For example, you could have the application generate a unique id with some kind of run time utility.

In this case, I'm presenting to you one way of faking an Oracle auto-increment column type. In a nutshell:
1. Use a sequence
2. Use a trigger

Get to the code, already
The table:

		  
	CREATE TABLE my_table (
		row_id INTEGER,
		user_id VARCHAR(256),
		data VARCHAR(256)
	)
		  

		  
	/* Sequence to autoincrement */
	CREATE SEQUENCE seq_for_my_table
	START WITH 1;

	/* Trigger to auto increment row_id */
	CREATE OR REPLACE TRIGGER my_table_trigger_insert
	BEFORE INSERT ON my_table
	FOR EACH ROW
		DECLARE m_no NUMBER;
	BEGIN
		SELECT seq_for_my_table.nextval INTO :new.row_id FROM dual;	
	END;

	INSERT INTO my_table (user_id, data) 
	VALUES ('jose', 'This is some data');
		

So, where is row_id? It is being auto incremented! Simple as that.

Summary
There are reasons not to couple your DB and your Application, but most application require this coupling. Otherwise, Microsoft would not sell a whole business solution to generic problems. Why would Oracle want you to use Oracle products?

Java J2EE promises to rid of this coupling, but, the truth is that is not the case. I'm still waiting for the "write once, run anywhere" promise to be fullfilled.

Anyway, if you are using an Oracle database and want to have an auto incremented column (handy sometimes), this is one way of doing it.

I hope this tip is of some use to some one.