Microsoft T-SQL to Oracle PL/SQL translation?

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.

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....

If you replace your ISNULL and NVL nonsense with COALESCE, it'll work in T-SQL and PL/SQL!

The most important differences for plain T-SQL are: NVL replaces ISNULL SYSDATE replaces GETDATE() CONVERT is not supported Identity columns must be replaced with sequences.

I have done a few SQL server to oracle migrations. There is no way to migrate without rewriting the backend code. Too many differences between the 2 databases and more importantly differences between the 2 mind sets of the programmers.

Many managers think that the 2 are interchangeable, I have had managers ask me to copy the stored procedures from SQL server and compile them in oracle, not a clue! Toad is by far the best tool on the market for supporting an oracle application. SQL developer is ok but was disappointing compared to toad.

I hope that oracle will catch their product up to toad one day but it is not there yet. Have a good day :) chances are if you are migrating to oracle it is for a reason and in order to meet that requirement you will need to rewrite the back end code or you will have many issues.

I have to things to mention. 1) When I worked on Oracle 8, you could not do "Select @Result", you had to instead use the dummy table as follows "Select @Result from dual". Not sure if that ridiculousness still exists.2) In the Oracle world they seem to love cursors and you better read up on them, they use them all the time AFAICS.

Good luck and enjoy, it is not that different to MS SQL. Thankfully, I do not have to work with it anymore and I am back in the warm comfort of MS tools.

It's not trivial to map them back and forth, so I doubt there's a tool that does it automatically. But this link might help you out: vyaskn.tripod.com/oracle_sql_server_diff....

It's not trivial but Oracle Workbench will take most T-SQL and convert it – Mark Brady Mar 11 '09 at 21:39 Not cheap ($995), but this can do it: swissql. Com/products/sql-translator/sql-converter. Html – Liao Nov 6 '09 at 7:29.

If you're doing a one-off conversion, rather than trying to support two versions, you must look at Oracle Migration Workbench. This tool works with Oracle's SQLDeveloper (which you really should have if you are working with Oracle). This does a conversion of the schema, data, and some of the T-SQL to PL/SQL.

Knowing both well, I found it did about an 80% job. Good enough to make it worth while to convert the bulk of procedures, and hand convert the remainder "tougher" unknown parts.

Not cheap ($995) but this tool works great: swissql.com/products/sql-translator/sql-....

A few people have mentioned here converting back and forward. I do not know of a tool to convert from MSSQL to Oracle, but I used the free MS tool to convert a Oracle db to MSSQL and it worked for me and converted a large db with no problems I can call. It is similar to the Access to MSSQL tool that MS also provide for free.Enjoy.

In Oracle SQL Developer, there is a tool called Translation Scratch Editor. You can find it from Tools > Migration. The Oracle SQL Developer is a free download from Oracle and it is an easy install.

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 > Translation Scratch Editor). Then, just enter your T-SQL, choose the correct translation in the dropdownlist (it should default to 'T-SQL to PL/SQL'), and convert it.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.