Syntax differences between Oracle (PLSQL) and Microsoft SQL Server (T-SQL)

Toad Data Modeler
1

Hi, We are currently evaluating the Toad Data Modeller (TDM) product at my place of employment on a 30 day trial. We are very impressed with the product.

As part of our evaluation we have been reverse engineering some Oracle database schemas, then converting the physical model to Microsoft SQL Server. Having generated the DDL scripts and verified them,
we have noticed a number of errors due to the syntax difference between Oracle (PLSQL) and Microsoft SQL Server (T-SQL). For example the difference in syntax for cursors and exception handling between the two products.

The product versions we are using in this particular case are Oracle 12c R2 and SQL Server 2017. However this problem will also exist with other versions of the products.

What we are trying to acertain is are there any Quest products which will automatically convert the code between the two database platforms (Oracle (PLSQL) to Microsoft SQL Server (T-SQL)) or assist in this process. Otherwise it would potentially be a manual very time consuming process.

We found some freeware called SQLLINES, which makes a decent job of it, but it's not foolproof, and does not sort all issues. For example exception handling code is converted as is, and the resulting converted DDL script would fail if it is run against a Microsoft SQL Server database.

Basically if one of our customers wanted to migrate from their current Oracle database platform to another database platform for whatever reason, and there was a lot of oracle plsql code involved we would more than likely recommend that they stay with the same database platform,
and migrate/upgrade to a newer supported version of Oracle. If they definitely wanted to migrate to a different database platform like SQL server this could be very time consuming/costly sorting out all the code syntax to work in the new database platform.

Ideally we will want to buy all our products and support packages from Quest. The other Quest product we have also started to evaluate is Toad data point for migrating the actual data, which seems to do the job well.

Does anyone know if Quest have any tools which can help with this issue?

Thanks in advance

Steve

2

Good morning Steve,

unfortunatelly, as far as I know, there is no such product that could perform such complex operations that you describe.
Yes, Toad Data Modeler can convert one database into other (Reverse DB1 > Convert DB1 to DB2 > Generate DB2 DDL script), but as I'm aware, there is no ready to use solution for your needs.
For now, that's all I can help. I'll escalate the question and let you know if there is something I've missed.

Best regards.

Michal

3

Hi Michal,
Thanks for your reply. Yes if you could escalate my question that would be appreciated. If you get any feedback, please let me know the response.

Thanks in advance

Steve

4

I don't think you will find any software that can reliably migrate stored procedures from Oracle to SQL Server.

The languages (and their capabilities) and best practices are simply too far apart that any automatic conversion could ever come up with an acceptable solution (regardless in which direction).

Even if you find a tool that converts your functions, procedures and triggers, I am quite certain that you will end up with an inefficient and non-scalable solution.

5

Hi,
Thanks for your feedback. These were also the thoughts we had. It does make you think very carefully if you want to migrate to SQL server from oracle when there are a lot of stored procedures involved. In these cases probably best to stay on same database platform.

Thanks again.

Steve