NEW!!! Subscribe to my
|Want to keep in
touch with the latest in SQL Server world? Email firstname.lastname@example.org with 'subscribe' in the subject
Review of AdventNet SwisSQL SQL Server to Oracle Edition 3.0
In a previous review I looked at "Adventnet SwisSQL Sybase to SQL Server Migration tool 2.1". That was a Java based desktop application that migrates Sybase SQL scripts to Microsoft's T-SQL. In this article, I am going to look at a similar tool (infact, same tool), except that it converts Microsoft SQL Server T-SQL scripts to Oracle's PL/SQL. This product comes from the same software vendor, and is called "SQL Server to Oracle Edition 3.0". This product eases the process of migration by automatically converting Microsoft Transact-SQL based Procedures, Functions and Triggers to Oracle PL/SQL.
As you probably know, Microsoft's SQL dialect T-SQL (Transact-SQL) is hugely different from Oracle's PL/SQL. Converting SQL code from T-SQL to PL/SQL or vice versa is very difficult, and painfully time consuming. Using a tool like "Adventnet SwisSQL SQL Server to Oracle" can help cut the migration time, there by saving man hours and money. At the same time it also provides a consistent conversion of code across all modules.
Now lets get straight to the review. You can download an evaluation version from AdventNet website. This evaluation version is good for 30 days, and can convert upto 2000 lines of stored procedure code.
Make sure you download the installation which includes the Java Runtime Environment (JRE). Though the download with JRE is bulky (14 MB, compared to 3.3 MB of installation file without JRE), it is worth downloading, as it'll save you time configuring the required JRE settings manually.
This migration tool supports the SQL conversion from and to the following RDBMS versions:
Microsoft SQL Server 2000
Oracle 8.1.6, 8.1.7, 9i, and 10g
Installation is pretty straight forward, apart from the following issues. If you are running the setup on a Windows XP SP2 machine, XP will warn you saying the publisher for this software is 'Unknown'. This product is based on Java, and requires Java Runtime Environment. But Windows XP SP2 firewalls blocks Java from running. For this product to work, you have to make sure you are not blocking Java.
Here's how the application user interface looks like:
This tools allows you to migrate your database objects using two different methods. First method is to provide the tool with T-SQL script files, that is, offline migration. The other option is to fetch the SQL Server database objects directly from an SQL Server instance (online migration). This tool can connect to a SQL server using either JDBC or ODBC.
As you can see from the above screen shot, the application window consists of two main areas:
The left hand side pane allows you to provide the input T-SQL scripts, and the right hand side pane provides access to the converted Oracle PL/SQL scripts.
This tool is pretty straight forward to use. Using the "Add" button on the left hand side pane, you can load your input T-SQL scripts into the application. You can select one or more script files from the 'File Open' dialog box. Similarly, you can use the "Wizard" button on the right hand side of the window, to connect to an SQL Server, and fetch database objects for conversion.
Using the "Remove" button on the left hand side pane, you can delete the scripts that are already selected.
Using the "View TSQL" button, you can view the scripts inside the files that are currently loaded.
Once, all the required scripts are loaded into the application, you can use the "Migrate" button from the top row of buttons, to convert all the loaded files from T-SQL to PL/SQL. Alternatively, you can select one or more files, and convert only those files, using the "Migrate Selected Files" button.
During the conversion process, a "Migration Progress Viewer" appears. This windows shows you the current status of the conversion. You can access detailed information about the performed conversion, by clicking on the "View Report" button. Here's a screen shot of "Migration Progress Viewer":
There's a "Parse TSQL files" button in the top row of buttons, that I found quite useful. This feature is useful for checking the input files for syntax errors and other potential problems, before the actual conversion. This functionality can also be invoked from the Action > Examine menu item, or by clicking Ctrl + E.
Press "F1" to access the help files for this product. The "User Guide" in the help file provides, step by step instructions for migrating your SQL Serverdatabase objects to Oracle.
Here's an example, of a T-SQL script, and an equivalent PL/SQL script produced by this tool. Notice how the RAISERROR is converted:
IF EXISTS ( SELECT name from sysobjects where name='TestProcedure' AND type='P')
DROP PROCEDURE TestProcedure
CREATE PROCEDURE dbo.TestProcedure
SET NOCOUNT ON
IF @i < 0
RAISERROR ( '@i CANNOT BE LESS THAN 0.', 16, 1 )
WHERE id = @i
Equivalent Oracle PL/SQL version, generated by SwisSQL SQL Server to Oracle Migration Tool:
CREATE OR REPLACE PROCEDURE TestProcedure
i IN INT DEFAULT NULL,
RCT1 IN OUT GLOBALPKG.RCT1
IF i < 0 THEN
RAISE_APPLICATION_ERROR(-20001, '@i CANNOT BE LESS THAN 0.' );
OPEN RCT1 FOR
WHERE id = TestProcedure.i;
The above is just a simple example, but this product is capable of converting more complex queries, stored procedures and functions into PL/SQL.
Here are the source T-SQL and the equivalent PL/SQL script produced by this tool:
Source T-SQL script file
Resulting PL/SQL script file
As you can see these scripts demonstrate how this tool converts T-SQL functions and stored procedures into PL/SQL.
The only downside I can think of is that, this product is a Java based application, and as such does not conform to Microsoft Windows User Interface guidelines. For example, you may not be able to close a dialog box by pressing the Esc key. Or Ctrl + C may not copy text to clipboard, for example. But don't get discouraged by this comment, as the product itself is a powerful tool for the DBA and database developer community involved in database migrations. I was assured by SwisSQL that they are working on improving the user interface of the product.
Here's a tip. By default, every time you start this application, a command prompt window also starts up, and stays open, as long as the main application is open. If this annoys you, then there's a way to prevent this command prompt window from staying visible. Here's how:
1) Edit the "runTSQL2PLSP.bat" file under /bin/ folder
2) Modify the option as shown below:
Now start the product. The command prompt window will be displayed only for a moment and it will disappear.
Well, this is it for now. You can download a free evaluation version of AdventNet SwisSQL SQL Server to Oracle Migration Tool, from here.Note that SwisSQL provides free technical support during the evaluation period.