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.
