Oracle Migration Workbench - Part 5

Wednesday Dec 22nd 2004 by Steve Callan

Part five of this series looks at issues related to the SQL Server to Oracle migration process.

At the end of Part Four, we were ready to create the Oracle model of the Northwind database "imported" from a SQL Server 2000 instance. In this part of the series, we will continue the SQL Server migration and look at some issues related to the SQL Server to Oracle migration process.

Migrating a SQL Server database

At the end of Part Four, we saw the Capture Wizard welcome window. Before getting into the models, let's re-visit a topic mentioned in a previous article. With the SQL Server plug-in successfully installed, one of the items I said we would look at was the Help menu. When the previous setup for MySQL was replaced, Migration Workbench also replaced the help menu contents, so if you need to reference documentation for another source database system (MySQL, DB2, etc.) not currently "loaded" in the utility, you can look in the docs directory under OMWB home and proceed from there. The SQL Server specific help menu appears as shown below.

When I select a guide, the browser that appears is Internet Explorer, even though in the last article I had set my preference to Netscape. Apparently, the file is overwritten. Again, this setting is found in the state.properties file (edit the BROWSER_PATH variable).

The Source model versus the Oracle model

Notice the comparison in structure between the source model and the Oracle model.

SQL Server Source Model

Oracle Model

Some objects are mapped one-to-one, that is, there is the same exact number of items in both models under a specific category. The check constraints are an example of this. The eight constraints in the Northwind database were directly mapped into the Oracle model. In the screen shot below, you can see the eight constraints in Northwind.

The Oracle model representation is shown below.

Do you have to do anything with the check constraints (in this case) because they match up so well? It depends, because SQL Server has its own version or flavor of SQL. Upon inspection of the CK_BIRTHDATE constraint (you can use either model, click on the constraint name to make the code appear in the right pane), you'll see a "getdate()" function in the Constraint Details section.

For Oracle, this "getdate()" function is a no-go, and this leads into what may be a complex at a minimum, tedious to be sure task: converting SQL Server code into Oracle compliant code, and this topic is covered in detail in the Microsoft SQLServer 2000 Reference Guide (accessible via the Help menu). In fact, there is a lot of detail and it appears many SQL Server code constructs need to be converted before the code will successfully compile in Oracle.

More than likely, any database migration/conversion project is going to be using a schema (or more than one) that was developed with only a single RDBMS product in mind. For example, the code to retrieve the top ten items in a SQL Server function/procedure/trigger could be written in generic ANSI SQL that Oracle can use (and vice versa). Another potentially edit-intensive conversion process concerns the use of quoted identifiers. Many of the views in the SQL Server databases (Northwind and pubs) use "Some Name" as an object name instead of a single unquoted string like somename. If you do not like using quoted identifiers (which Oracle supports), this is where a coding standards guide or policy pays off handsomely.

So, what has to be converted from SQL Server to make the code syntax Oracle compliant? After perusing the reference guide, you may conclude that this question should have been phrased as "So, what doesn't have to be converted?" Oracle always has a FROM clause in SELECT statements, whereas SQL Server does not. Any "getdate()" reference in SQL Server then becomes "SELECT sysdate FROM dual." Which method is better first begs the question of defining what "better" means, but the idea of a one word named function that even looks like a function with the "()" part is very appealing if you are a developer constantly having to select sysdate from the dual table. On the other hand, having the dual table is useful for performing calculations.

Going back to SQL Server's Query Analyzer tool for a moment, let's look at what SQL Server provides in comparison to Oracle's SQL*Plus utility. What does it take to create, alter or drop, say, a trigger? The Query Analyzer presents the code in a ready to edit format, all of it right there in front of you. The woefully inadequate SQL*Plus lets you perform a select statement to see the code, and then you have to type it back in along with the changes.

Here is an example from SQL Server, using the CustOrdersDetail stored procedure. With a simple right-click/click maneuver (right-click on the item, then select Create, Alter or Drop from the menu), you can go from what is shown below -

to a ready for editing window.

This is another of those "Gee, I wish Oracle did that" examples I mentioned in Part Four.

Picking up where we left off in Part Four

After starting OMWB, select Action>Capture Source Database and the Capture Wizard starts. The following series of screen shots is similar to what we already saw with the MySQL example, so they are shown without comment.

Click for larger image

Click for larger image

Click for larger image

After clicking "Yes" at this last window, let's generate a migration report and view its contents. You generate the reports via the Report menu and OMWB informs you as to where they are located.

Clicking OK results in the report being presented to you in your browser. Let's look at the details for Northwind's stored procedures. The first line item has to do with a "set ROWCOUNT" statement in the Ten_Most_Expensive_Products procedure. Oracle does not support "set ROWCOUNT" which is what SQL Server uses to return (in this case) the top ten, umm, most expensive products.

The main body of the original code is:

ALTER  procedure "Ten Most Expensive Products" AS
SELECT Products.ProductName AS TenMostExpensiveProducts, Products.UnitPrice
FROM Products
ORDER BY Products.UnitPrice DESC

To perform the manual conversion, you have at least two options.

Option 1) Modify the original code in SQL Server (or Oracle) to something you can immediately use in Oracle. You can simply run the SELECT part of the code and see that any product with a price greater than 44 is going to be in the top ten. Of course, you will be performing regular and frequent checks to ensure "44" is the correct value to distinguish the top ten most expensive products from the rest.

The kludged code is shown below.

ALTER procedure "Ten Most Expensive Products" AS
SELECT Products.ProductName AS TenMostExpensiveProducts, Products.UnitPrice
FROM Products
WHERE UnitPrice > 44
ORDER BY Products.UnitPrice DESC

Option 2) Modify the original code in SQL Server (or Oracle) to use ANSI SQL standards or syntax. This may or may not be simple, depending on your knowledge of what ANSI SQL looks like when compared to SQL Server or Oracle SQL. If you do not know how to reformat "set ROWCOUNT" to ANSI SQL (or a comparable construct in Oracle), but need to get this migration project done like yesterday, do what you do know and go back and fix it later.

What else is in the report?

Looking at the Sales by Category view, the parser does not like the comma at the end of line two (shown highlighted after Products.ProductName).

Click for full image

Click for full image

Would a simple "make it one line and save it" fix remove this error? Let's see. Here's the altered code saved in SQL Server. My original "out of the box" create an Oracle model had 22 errors.

Click for full image

Running the Migration Workbench capture process again results in some errors going away. Now there are only 20 errors (I made a few other changes, including the "set ROWCOUNT" conversion).

Click for larger image

The "set ROWCOUNT" problem in the ten most expensive products stored procedure went away, but Migration Workbench still does not like the comma in the view. And just as an experiment, adding a space before the comma to see if the editing makes any difference, only results in the same error, but now it is at position 77 instead of 76.

What is wrong with this line (and others like it in the other error-flagged views)? The nondescript parse error does little to help you fix what Migration Workbench does not like. There are two approaches to this problem: ignore it and continue on with the migration, or figure out what, exactly, the parser does not like. Moving on with "Migrate to Oracle" via the menu path shown below, the end result shows a smaller number of errors, but they are spread out across more areas (stored procedures, indexes and views).

Migration Workbench will present an editable field where you can fix errors on the spot. The "getdate()" function is easy to fix in this context because all it requires is replacing it with sysdate. The other errors, one of which is shown, comes back to the views that Migration Workbench did not like.

An easily correctable error:

An error which involves fixing code in SQL Server or in the model.

The end result showing a breakdown of failed objects by object type is shown below.

Failed indexes are easy to fix - or not, because you can always work without them. The failed stored procedures are of concern, so if those were encountered in a real or production database, you would probably want to fix them before going live with the migrated data.

A query from SQL*Plus confirms the two failed stored procedures, but there is also a view that appears invalid.

Logging in as "sa" and compiling the view results in the following:

The same approach (trying to fix one of the procedures by compiling) fails, and the error reflects what was shown in the migration report.

In general, the report table should be read with care. The ten views that failed in the "Create Oracle Model" phase means only seven of the 17 original views actually made it into Oracle. Of the seven views present in Oracle, one of them had to be compiled to change its status from INVALID to VALID.

What's in the reference guide?

Quite a bit, actually. The troubleshooting section is fairly extensive, but it fails to address the parsing errors which were present in the Oracle model (before the data was actually migrated into Oracle itself). There is also an extensive side-by-side comparison of SQL Server and Oracle, plus plenty of code examples. Be forewarned, however, that some examples are incorrect. Using the pubs database as one of the code examples:

This doesn't work

This works

Although presented in a text format, you will still be able to notice (even more) neat things SQL Server does or supports. For example, the phone number constraint of


in SQL Server would have to be dealt with by using a combination of SUBSTR, TRANSLATE and LIKE, or use of a trigger and PL/SQL in Oracle.

The title of the reference guide includes Sybase in it, but for the most part, the guide is all about SQL Server versus Oracle. What happened to Sybase? It is not as if Sybase was omitted in the documentation, but rather that Sybase used to be the "SQL Server" product and the development/code base is not that different today. In the dark ages before high speed Internet service, Sybase partnered with Microsoft because Sybase needed help marketing its database product, and Microsoft needed help with its Office product (and its lack of a database component). The Microsoft version of SQL Server overtook the Sybase version, and the companies parted ways, with Sybase renaming its product. Anyway, because Sybase and SQL Server share a common history, Oracle did not need to create a separate reference guide for Sybase.

In Closing

If you need to get a migration project up and running in short order, Oracle Migration Workbench, even when using a more sophisticated RDBMS product such as SQL Server as the source, can get you pretty close to where you need to be without too much difficulty. Unfortunately, using a relatively simple database such as Northwind causes heartburn for Migration Workbench. Are there third party products that can do a better job? In the next part of this series, we will look at one such product - SwisSQL - and examine its ability to do two things: migrate from SQL Server to Oracle and perform a migration in the other direction. Although Migration Workbench is free, it is a one-way ticket.

» See All Articles by Columnist Steve Callan

Mobile Site | Full Site