x

How do I port a SqlServer application that uses IDENTITY columns to oracle?

I do not wish to have to change the application code if possible. The current data access in done with Ado.net mostly Datasets and table adaptors.

Most (all) of the current sql statements in the application are simple enough to work on both oracle and sqlserver with no changes. (apart from a string.replace() to convert @ in parameter names)

more ▼

asked Oct 29 '09 at 12:51 PM in Default

Ian Ringrose gravatar image

Ian Ringrose
225 6 6 8

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

In Oracle, you create a number column and use a sequence. A good reference for you would likely be Oracle for SQL Server Developers, Vol. 4: Identity Columns & Sequences.

more ▼

answered Oct 29 '09 at 12:54 PM

Jonah H. Harris gravatar image

Jonah H. Harris
395 3

Do I then need to change my applicaion code so it makes use of a Sequence?
Oct 29 '09 at 01:51 PM Ian Ringrose
The sequence can be incremented via a BEFORE INSERT trigger (which you would have to write). It depends on what your application did with the identity column. Many times, you don't need to change the app code to handle that difference though.
Oct 29 '09 at 02:41 PM Jonah H. Harris
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x341
x31
x4
x2

asked: Oct 29 '09 at 12:51 PM

Seen: 697 times

Last Updated: Oct 29 '09 at 12:51 PM