Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Dec 22, 2004

Oracle Migration Workbench - Part 5 - Page 3

By Steve Callan

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:

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

Oracle Archives

Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM