|
Click here to find out the top 15 SQL Server books purchased by this site's visitors! NEW |
| Want to keep in touch with the latest in SQL Server world? Email vyaskn@hotmail.com with 'subscribe' in the subject line |
|
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. |
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:
T-SQL:
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
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
)
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;
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
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 AdventNet SwisSQL SQL Server to Oracle Migration Tool, from here.Note that SwisSQL provides free technical support during the evaluation period.