Adding an Auto Number to Oracle 10g Database

It is common to include a primary key when creating a database table. The primary key is a unique number or field that uniquely identifies each record. Using MS Access, the process of automatically incrementing the primary key for each new record added is a simple process, you simply choose "auto number" as the data type. In Oracle 10g, it's not quit that simple but is still an easy process. You simply need to create an individual sequence number for each table containing a primary key that you want to control. Using SQL, you can call [Sequence Number Name].nextval to generate an automated number you can insert into a new record. Read on to learn how I went about accomplishing this and to view a portion of C# code from an application I wrote, which accessed the Oracle database and inserted a record using an Oracle sequence number I created within Oracle 10g.

Assuming you already have Oracle 10g installed, you'll need to access the Database Home Page by clicking on Start --> All Programs --> Oracle Database 10g Express Edition --> Go To Database Home Page, and then log in using the user name and password you selected when you set up the database.

Once you have logged in, you'll see graphical buttons/selections labeled Administration, Object Browser, SQL, Utilities and Application Builder. On the right side of each graphic is a small triangle pointed down, indicating a drop down list of options is available. Click on the drop down list for Object Browser, using your mouse highlight Create, then select Sequence.

Type a name for the new sequence number such as: [table name]_seq. Then fill in the remaining fields. Note that if you already have existing records, you'll have to be sure you have the correct value in the Start With column. I left Number to Cache blank. After filling in the fields, select Next then Create. Your sequence number is now ready to be used.
I created a table called Components. The table contained the following headings and data.

Primary Key	PARTID	PARTNUM	PARTDESC
1	2889	G533044-1	RF Cable
2	3442	G533688-1	RF Cable Terminator

Using C#, I've pulled the below code snippets which interacted with this table and used the sequence number I created, which was called COMP_SEQ with a starting value of 3.

using Oracle.DataAccess.Client;
...
string oradb = "Data Source=172.xx.xxx.xx;User ID=mrb;Password=mrb;";

OracleConnection oraConn = new OracleConnection(oradb); 
oraConn.Open(); 

// Insert new record into the table Components using Oracle sequence number COMP_SEQ

OracleTransaction ora_addToComponents = oraConn.BeginTransaction();
OracleCommand oraCommand1 = oraConn.CreateCommand();
oraCommand1.CommandText = "INSET INTO ASR11COMPONENTS
                                                      values (COMP_SEQ.nextval, '222', '333', 'RF Tester')";
oraCommand1.ExecuteNonQuery();
ora_addToComponents.Commit();

oraConn.Close();