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.
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
SET ROWCOUNT 10
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.
» 
Comment and Contribute