Microsoft T-SQL to Oracle SQL translation

I've worked with T-SQL for years but I've just moved to an organisation that is going to require writing some Oracle stuff, probably just simple CRUD operations at least until I find my feet. I'm not going to be migrating databases from one to the other simply interacting with existing Oracle databases from an Application Development perspective. Is there are tool or utility available to easily translate T-SQL into Oracle SQL, a keyword mapper is the sort of thing I'm looking for.

P.S. I'm too lazy to RTFM, besides it's not going to be a big part of my role so I just want something to get me up to speed a little faster.

Solution 1:

The language difference listed so far are trivial compared to the logical differences. Anyone can lookup NVL. What's hard to lookup is

DDL

In SQL server you manipulate your schema, anywhere, anytime, with little or no fuss.

In Oracle, we don't like DDL in stored procedures so you have jump through hoops. You need to use EXECUTE IMMEDIATE to perform a DDL function.

Temp Tables

IN SQL Server when the logic becomes a bit tough, the common thing is to shortcut the sql and have it resolved to a temp table and then the next step is done using that temp table. MSSS makes it very easy to do this.

In Oracle we don't like that. By forcing an intermediate result you completely prevent the Optimizer from finding a shortcut for you. BUT If you must stop halfway and persist the intermediate results Oracle wants you to make the temp table in advance, not on the fly.

Locks

In MSSS you worry about locking, you have nolock hints to apply to DML, you have lock escalation to reduce the count of locks.

In Oracle we don't worry about these in that way.

Read Commited

Until recently MSSS didn't fully handle Read Committed isolation so you worried about dirty reads.

Oracle has been that way for decades.

etc

MSSS has no concept of Bitmap indexes, IOT, Table Clusters, Single Table hash clusters, non unique indexes enforcing unique constraints....

Solution 2:

I get the impression most answers focus on migrating an entire database or just point to some differences between T-SQL and PL/SQL. I recently had the same problem. The Oracle database exists, but I need to convert a whole load of T-SQL scripts to PL/SQL.

I installed Oracle SQL Developer and ran the Translation Scratch Editor (Tools > Migration > Scratch Editor).

Then, just enter your T-SQL, choose the correct translation in the dropdown-list (it should default to 'T-SQL to PL/SQL'), and convert it.