Oracle Migration Workbench - Part 5
December 22, 2004
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.
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.