I am looking to modify a .NET application that has a SQL Server backend to make it work with Oracle. Has anyone done this? Any pitfalls that I can avoid?
views:
72answers:
2
+5
A:
It really depends on how you are accessing the database, and how much of your code is database-specific.
If you are using Linq to SQL, you will have to migrate to a different ORM, as Linq to SQL only supports SQL Server.
If you are using stored procedures or DDL statements, you may have to rewrite substantial portions of these to get them to work in Oracle.
If your SQL statements are all ANSI SQL compliant, and you are using ADO.NET to access the database, all you may need to do is change the connection strings.
Some of the datatypes have different names in Oracle.
Using Entity Framework might reduce some of the pain points; since EF supports multiple database providers, it should abstract away much of the compatibility pain.
Generally speaking, Oracle requires more expertise, maintenance and tuning than SQL Server. Having a DBA can really be beneficial.
Robert Harvey
2010-01-29 18:40:32
It's not a simple port; the concurrency (locking) models between SQL Server and DB2, for example, are much more similar to each other than either is to Oracle. SQL Server apps tend to use a great many temporary tables and commit with great frequency to release locks; all that does is add latency to an Oracle system.
Adam Musch
2010-01-29 19:37:32
Oracle Data Provider for .NET is what I used and it worked pretty well, as the application had some pretty straightforward operations going on, nothing complex. Also used SQL Developer to migrate and convert the SQL database to Oracle, which did a pretty decent job.I did have to go through and cleanup the migrated code to ensure it was functioning as intended, but that wasnt too bad.
2010-03-16 19:57:06
+1
A:
One of the big differences you'll find, other than the list from @Robert Harvey, is generating primary keys: SQLServer has autoincrement, which you'll have to do yourself in oracle (at least, up to Oracle 10 - I'm not familiar with 11). Typically, you'll have to use a sequence; for consistency you can create on insert triggers, or just use the sequence.nextval in your insert statement.
chris
2010-01-29 18:50:59
11g doesn't have an autoincrement feature, and I would put money on 12g not having it, whenever it comes out.
APC
2010-01-30 14:10:53