What are the common problems you expect to hit when you have to port an application from SqlServer to Oracle?
What are the best solutions to these problems?
(Please consider creating separate questions for detailed answers for each problem and linking to these questions.)
As I am from an ISV background, I am assuming the application will have to continue to support SQLSever as well as adding support for Oracle. Including all feature versions, so development/testing process will be part of the solution.
Answer by Andrew Mobbs ·
Oh, where to start...
If there's a lot of stored procedures in the application, face the fact early on that you're looking at a rewrite, not a port. If you don't, you'll only discover this the hard way later on at greater cost, more late nights and missed deadlines.
Don't act under the illusion that there's such a thing as a cross-platform application. At least, not a good cross-platform application. You'll limit yourself on both platforms if you stick to a common subset of functionality in SQL.
Result sets are the obvious difference, you need to deal with ref cursors rather than expect it to be implicit.
TOP-n queries need to be rewritten.
Locking is different - it acts like Read Committed Snapshot Isolation by default (the only other option is serialized, and that's hardly ever used). Readers never block.
SELECT FROM DUAL rather than just SELECT
There's a temptation to replicate T-SQL exception handling with a WHEN OTHERS THEN in Oracle. Don't do this, it's a quick fix that will lead to trouble later.
Your developers need to be trained to write good PL/SQL, rather than attempting to continue to write T-SQL in a slightly different dialect. PL/SQL is a much richer, more powerful programming language.
Use PL/SQL packages.
There's nothing that exactly maps to a SQL Server 'database' in Oracle. Don't try to replicate this with different instances, you're better off using users and tablespaces to achieve the separation.
Cursors aren't the great evil in Oracle that they're sometimes considered in SQL Server. That doesn't mean that you can shouldn't still try to write set based SQL, but if it's a choice between a WHILE loop to iterate over results and a cursor, use the cursor.
Learn the tools and idioms of the different environment to produce an efficient, competative solution; use PL/SQL collections, exception types, bulk binds, packages. Create tables as the default heap-type in Oracle unless you know you want an IOT, don't create everything as an IOT just because they look like tables with a clustered index.