Click here to Skip to main content
13,295,472 members (49,978 online)
Click here to Skip to main content
Add your own
alternative version


7 bookmarked
Posted 8 Jan 2012

Mimicking SQL Server identity column in Oracle

, 8 Jan 2012
Rate this:
Please Sign up or sign in to vote.
This article describes how to use a sequence to mimic the SQL Server identity column in Oracle.


Every now and then a question is asked, how do I define an identity (auto-increment) column in Oracle like in SQL Server? There's no such concept in Oracle directly, but similar behavior can easily be created.

Unique numbering in Oracle

An identity definition in SQL Server is bound to a single column which is auto-numbered at insertion time. Typically the column is defined with an identity starting from 1 incremented by 1. After the column definition, SQL Server takes care of giving the row a 'unique' value in the identity column.

In Oracle, an automatically generated numerical value cannot be defined in table creation. Instead, Oracle has a special object type called sequence to generate unique numbers. One big difference is that the sequence isn't bound to any table or column, so it can be used wherever appropriate. So to achieve a similar behavior as in SQL Server, the value produced by a sequence must be inserted to a column of a row using code.

Let's create a small test table to use:

-- Create the table for testing
CREATE TABLE IdentityTest (
   Text VARCHAR2(100) NOT NULL

The idea is to add rows to this table by specifying a value only for the text column. The ID column should get a value 'automatically'.

The next step is to create a sequence:

-- Create a sequence

The above statement creates a new sequence object and the numbering starts from 1 incrementing always by 1. One important thing to notice is the NOCYCLE definition. This means that when the upper limit of a sequence is reached, it doesn't start giving numbers from the beginning. Instead, trying to get a new value from the sequence generates an error. This is the default behavior of a sequence and shouldn't be modified if the sequence is supposed to produce key values.

Now to combining the table column and the sequence. A very easy and invisible way is to create a trigger to the table:

-- Create a trigger to add a unique value to the id
   SELECT SeqIdentityTest.NEXTVAL
   FROM Dual;

The trigger is fired separately for each row that is inserted into IdentityTest. The body of the trigger simply fetches a new value from the sequence and places the result in :NEW.Id. :NEW refers to the row as it's going to be inserted into the table after the trigger execution.

Since Oracle doesn't support a SELECT statement without a table to select from, dual is used. dual is a built-in table containing a single row so selecting from dual results always in one row.

Let's test what happens with Insert:

-- Test insert
INSERT INTO IdentityTest (Text) VALUES ('Test2');
SELECT * FROM IdentityTest;

The result is:

----- ----------
    1 Test2

As in SQL Server, sequence doesn't guarantee that every value the sequence has produced is actually found from the database. It only guarantees that the same value won't be supplied twice. For example, if a row is inserted to the IdentityTest table but the transaction is rolled back, the value sequence generated is gone.

How to know the generated value

It's nice that the ID column gets a value automatically, but what if the application needs to know what was the value given to the row? For example, if the table is a parent table and next the application needs to insert to a child table and use the primary key value from the parent table in the child table's foreign key column. It's necessary to get the value back to the calling application.

The included small demo program shows how the RETURNING clause can be used to get the value back to the client. It's well worth noticing that the RETURNING clause isn't supported with ADO.NET OleDbCommand with the msdaora provider. Because of this, ODP.NET is used in the demo application. ODP.NET can be found at Oracle Data Provider for .NET.

In order to use the application, define a proper connection string for the Oracle connection. The default connection string contains the already default port 1521, for instance XE. If the instance is correct, just add the proper user ID and password.

The code is as follows:

Oracle.DataAccess.Client.OracleConnection connection = new Oracle.DataAccess.Client.OracleConnection();
Oracle.DataAccess.Client.OracleCommand command = new Oracle.DataAccess.Client.OracleCommand();
Oracle.DataAccess.Client.OracleParameter parameter;
int rowsAffected;

connection.ConnectionString = this.ConnectionString.Text;
command.CommandText = "INSERT INTO IdentityTest (Text) VALUES (:text) RETURNING Id INTO :id";

parameter = new Oracle.DataAccess.Client.OracleParameter(":text", "Test 1");

parameter = new Oracle.DataAccess.Client.OracleParameter();
parameter.ParameterName = ":id";
parameter.DbType = System.Data.DbType.Int64;
parameter.Direction = System.Data.ParameterDirection.ReturnValue;

command.Connection = connection;

try {
   rowsAffected = command.ExecuteNonQuery();
   System.Windows.MessageBox.Show(string.Format("Added a row with id: {0}",
      "Operation succeeded");
} catch (System.Exception exception) {
      "Exception occurred",
} finally {

The key thing in the code is the Insert statement. As in the first test, it contains a value only for the second column. This value is delivered to the database using a parameter. The Insert statement is defined to return the inserted value from Id to a second parameter :id. This is achieved by adding:


to the end of the insert statement. The first parameter is a normal input parameter going from client to server. But the direction of the second parameter is defined as System.Data.ParameterDirection.ReturnValue so this means that the parameter value is filled during the call.

When the Insert is executed, the program shows the value for the ID field from the row that was inserted. Now the program can store this value and use it in subsequent calls to the database.


When replication is involved, automatically generated numbers are always an issue. SQL Server is quite strict when replicating tables with identity columns. Even though separate sequences can be used in two databases participating in replication in Oracle, the main problem still remains. How can we prevent duplicate values across databases?

One way is to give the sequences whole different number ranges in each database. The same solution that can be used with SQL Server. Depending on the situation, a more proper solution may be to create a composite key. This means that the key contains more than one column. For example, the first column could be a static value in the scope of a single database such as Site or Database ID, and the second column is the actual generated number. With this solution, the sequences may perfectly well produce same values but the integrity of the primary key isn't compromised even if rows are transferred from a database to another.

Since sequences are separate objects, a replicating database can actually also fetch the new value from a common sequence located in a single database. However, this also means that the databases are not independent anymore so this solution isn't typically used in replicating environments but in distributed databases.

Other mechanisms

Using a sequence isn't the only option. SQL Server has a NEWID() function to create a value for a uniqueidentifier column. Oracle has a similar (but not exactly the same) function called SYS_GUID(). In Oracle, this function produces a new globally unique RAW value. This function can be used similarly as the sequence in the example. The value of SYS_GUID() can be inserted using a trigger or by setting it as a default value for a primary key column. When default is used, no additional trigger has to be created. However, with defaults, there's always a risk that the client actually supplies the value so to be on the safe side, a trigger may have to be created. Getting the RAW value to the client follows the same rules as used in the example application.

When SYS_GUID() is used in replicating environments, a composite key isn't needed anymore since the value is globally unique. Each database can produce a new value and they won't overlap.


Some references for the article:


  • January 8, 2012: Article created.


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


About the Author

Finland Finland
No Biography provided

You may also be interested in...


Comments and Discussions

QuestionHandling Replication... Pin
Jason Vogel9-Jan-12 9:31
memberJason Vogel9-Jan-12 9:31 
AnswerRe: Handling Replication... Pin
Mika Wendelius9-Jan-12 10:06
mvpMika Wendelius9-Jan-12 10:06 
Smile | :) Yes that's also one perfectly valid solution. Done that also, but nowadays I personally prefer a composite key since the site (or db) information makes things very easy when the initial location doesn't need to be interpreted and it's also a good piece of information for potential data partitioning.
The need to optimize rises from a bad design.My articles[^]

GeneralRe: Handling Replication... Pin
Jason Vogel9-Jan-12 14:16
memberJason Vogel9-Jan-12 14:16 
GeneralRe: Handling Replication... Pin
Mika Wendelius10-Jan-12 9:08
mvpMika Wendelius10-Jan-12 9:08 
QuestionThoughts Pin
PIEBALDconsult9-Jan-12 5:13
memberPIEBALDconsult9-Jan-12 5:13 
AnswerRe: Thoughts Pin
Mika Wendelius9-Jan-12 8:34
memberMika Wendelius9-Jan-12 8:34 
GeneralMy vote of 5 Pin
Espen Harlinn9-Jan-12 5:10
memberEspen Harlinn9-Jan-12 5:10 
GeneralRe: My vote of 5 Pin
Mika Wendelius9-Jan-12 8:35
memberMika Wendelius9-Jan-12 8:35 
Questionvery nice Pin
Garth J Lancaster9-Jan-12 1:29
memberGarth J Lancaster9-Jan-12 1:29 
AnswerRe: very nice Pin
Mika Wendelius9-Jan-12 8:40
memberMika Wendelius9-Jan-12 8:40 
GeneralMy vote of 5 Pin
Jörgen Andersson9-Jan-12 0:01
memberJörgen Andersson9-Jan-12 0:01 
GeneralRe: My vote of 5 Pin
Mika Wendelius9-Jan-12 8:41
memberMika Wendelius9-Jan-12 8:41 
GeneralRe: My vote of 5 Pin
Jörgen Andersson9-Jan-12 11:48
memberJörgen Andersson9-Jan-12 11:48 
GeneralRe: My vote of 5 Pin
Mika Wendelius10-Jan-12 9:19
mvpMika Wendelius10-Jan-12 9:19 
GeneralRe: My vote of 5 Pin
Jörgen Andersson10-Jan-12 10:58
memberJörgen Andersson10-Jan-12 10:58 
GeneralRe: My vote of 5 Pin
Mika Wendelius10-Jan-12 11:05
mvpMika Wendelius10-Jan-12 11:05 
QuestionMy 5 Pin
Mehdi Gholam8-Jan-12 20:42
memberMehdi Gholam8-Jan-12 20:42 
AnswerRe: My 5 Pin
Mika Wendelius9-Jan-12 8:42
memberMika Wendelius9-Jan-12 8:42 

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

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

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.171207.1 | Last Updated 9 Jan 2012
Article Copyright 2012 by Wendelius
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid