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).
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.
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).
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:
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
"([0-9][0-9][0-9])[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9])"
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
» 
