Review of SwisSQL SQL Server to Oracle Migration Tool

Narayana Vyas Kondreddi

Microsoft Most Valuable Professional (SQL Server)

Jan 4, 2006

Source: http://vyaskn.tripod.com/review_adventnet_swissql_sql_server_oracle.htm

In a previous review I looked at "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 "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 SwisSQL 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":

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
GO

CREATE PROCEDURE dbo.TestProcedure
(
	@i int   
)
AS

SET NOCOUNT ON 

BEGIN 

	IF @i < 0 
		BEGIN 

			RAISERROR ( '@i CANNOT BE LESS THAN 0.', 16, 1 )
		END
	ELSE 

		BEGIN 

			
			SELECT
					 id,
					 Name
			FROM  MyTable 
			WHERE	 id  = @i
			
		END
END
GO

CREATE OR REPLACE PROCEDURE TestProcedure
(
	i                          IN      	INT DEFAULT NULL,
	RCT1                        IN  OUT 	GLOBALPKG.RCT1
)
AS
BEGIN   
	BEGIN  
		IF i < 0 THEN  
		BEGIN  
			RAISE_APPLICATION_ERROR(-20001,  '@i CANNOT BE LESS THAN 0.' );
		END;
		ELSE  
		BEGIN 
			
			OPEN RCT1 FOR 
			SELECT
					 id,
					 Name
			FROM  MyTable 
			WHERE	 id  = TestProcedure.i;
		END;
		END IF;
	END;
END;

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

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:

Original statement:

"%javahome%\bin\java"

Modified statement:

start %javahome%\bin\javaw

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 SwisSQL SQL Server to Oracle Migration Tool, from here. Note that SwisSQL provides free technical support during the evaluation period.