Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Migration tool from TSQL to PL/SQL? [closed]

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.

like image 938
elifiner Avatar asked Jun 29 '10 12:06

elifiner


People also ask

How do I create a migration repository in Oracle SQL Developer?

In SQL Developer, go to the Tools menu and select 'Migration -> Migrate…' Page 5 MIGRATING TO ORACLE DATABASE 4 This will launch a wizard that guides you through the process of creating a migration repository, capturing the objects, and performing the move to Oracle Database.

What is data migration in Oracle?

Oracle Cloud Infrastructure Database Migration (DMS) is a managed cloud service that provides a seamless user experience migrating production workloads from On-Premises Oracle Databases or Oracle Cloud Databases to Autonomous Databases in the Oracle Cloud.


3 Answers

  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.

like image 188
Will Marcouiller Avatar answered Sep 25 '22 23:09

Will Marcouiller


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!

like image 32
Jeff Maass Avatar answered Sep 21 '22 23:09

Jeff Maass


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.

like image 30
Diego Avatar answered Sep 25 '22 23:09

Diego