Migration tool from TSQL to PL/SQL?

Question

We need to migrate our database from MSSQL to Oracle and we have over 100 stored procedures written in PL/SQL TSQL. I know its a long shot, but has anybody ever successfully used an automatic migration tool to do the work? I've seen some tools on the web, but I have no way to judge their ability to handle various code. Any personal experience in the matter would help.

Answer 1

In the past I tried to do the opposite, but I'm afraid there's no 100% reliable way. Some features are unique to each RDBMS and can't be replicated by a tool (for example IDENTITY fields in SQL Server vs Sequences in Oracle). Considering that, at the end, I had to review each Stored Procedure to ensure it does what it's supposed to do, I realized it was better to plan a manual port and learn some PL/SQL while doing it.

Answer 2

I would suggest doing it by hand. First, to me, personally, a 100 doesn't sound too bad at all. I'd bet that your development follows patterns - using getutcdate() instead of current_timestamp, etc. This means, there probably are a few very repetitive problems that you and your team will be able to learn from. To me, this will hold the added benefit of teaching you PL/SQL nuances/extensions, which you'll probably need to learn in order to maintain / expand the new system.

Good luck!

Answer 3

1. SwisSQL - SQL Server to Oracle Migration Tool 3.5;
2. tsql to pl sql;
3. Convert SQL Server T-SQL to Oracle PL/SQL (syntax and equivalences);

However, there are some optimisation that may be required to perform after the conversion is done. So, you should consider revising the conversion after it is done.

EDIT #1

Another interesting tool would be:

Spectral Core's Full Convert Enterprise

Do you have experience with any of them?

Unfortunately not. I have just performed conversions to SQL Server using SSIS.

EDIT #2

There seems to be a more appropriate tool directly from Oracle that I would better trust.

Directly from Oracle: Migrating from Microsoft SQL Server to Oracle

And the core features:

SQL Developer Migration technology supports the following core features when migrating from Microsoft SQL Server:

• Automatically converts column data types to appropriate Oracle data types.
• Automatically resolves object name conflicts, such as conflicts with Oracle reserved words.
• Parses and transforms T-SQL stored procedures, functions, triggers, and views to Oracle PL/SQL.
• Provides advanced customization capabilities such as the ability to change data type mappings, delete and rename objects.
• Generates reports about the status of the migration.
• Generates the DDL scripts for the creation of the destination Oracle database.
• Generates scripts for data movement

• Displays informational, error, and warning messages about the migration in a progress window.

  1. So, I would suggest that you look out everything in details that is not above-mentionned;
  2. Take a good look to the information details provided by the tool;
  3. Test your stored procedures after they are converted.

If you take a look as what SQL Server doesn't offer in comparison to Oracle, these are points where I would look first. In order to know these differences, follow the 3. Convert SQL Server T-SQL to Oracle PL/SQL (syntax and equivalences) link above.