Oracle Migration Workbench FAQ

This FAQ provides information about Oracle Migration Workbench (Migration Workbench) under the headings outlined below. Each of these sections also provides specific information about the MS SQL Server, Sybase Adaptive Server (Sybase), and MS Access plugins where applicable.

Installation	Triggers
General Issues	Views
Data Types	Packages
Data Migration	Users
Migration Wizard	Tables
National Language Support	Rules
Parsing	User-Defined Types
Temporary Tables	Foreign Keys
Result Sets	Client Applications
Stored Procedures	Error Handling and Support
Global Variables	Y2K

Installation

Can I download the Migration Workbench from the Web?
The Migration Workbench is available for download from the Oracle Technology Network (OTN) Web site at http://technet.oracle.com/tech/migration/workbench
How do I create a new Oracle Migration Workbench Repository?
You must create a user in the Oracle database in which you wish to create the Oracle Migration Workbench Repository. The first time you log in to this user account, you will be asked if you would like to create the Oracle Migration Workbench Repository. The username, password, and service that you enter in the Oracle Migration Workbench Repository Login dialog box define where you want to create the repository. You can create the repository in an Oracle8 or Oracle8i Server.

General Issues

Click here for general issues about the Migration Workbench.
Click here for MS SQL Server and Sybase general issues.
Click here for MS Access general issues.

Can I migrate a single schema object?
The migration of individual objects is not supported.

Can I rename an object?
Yes. You can rename tablespaces and users in the Oracle Model using either the right mouse button or the Rename menu item in the Object Menu.

How are reserved words handled?
By default, reserved words have an underscore attached to them. An option is available in the General page of the Options dialog box that forces the Migration Workbench to use ANSI names for objects. This means that all object names are surrounded by double quotes when the Migration Wizard creates them in the destination Oracle database.
How do I define an Open Database Connectivity (ODBC) data source?
Double-click the ODBC icon in the Windows Control Panel and add a new data source.
What is the difference between the Migration Workbench and Oracle's Migration Companion CD-ROM?
Oracle's Migration Companion CD-ROM is a compilation of all the script-based migration toolkits, white papers, and MTI (Migration Technology Initiative) information produced by DMS up to April 1998. The script-based database kits involve executing scripts in sequence. The stored procedure converters are separate products that do not have any knowledge of what happened during the schema migration. For example, the stored procedure converters do not know what schema object names were changed.
The Migration Workbench is the latest migration technology offering from Oracle. It is written in Java and has a GUI front end that is intuitive and easy to use. It currently supports migrations from MS SQL Server 6.5, MS SQL Server 7.0, Sybase Adaptive Server 11, and MS Access. One of the biggest advantages of the Migration Workbench over the legacy script-based kits is that other migration capabilities can avail of the migration information stored in its repository. For example, the stored procedure converter can access the Oracle Migration Workbench Repository to determine what schema objects were renamed during the migration. In summary, the Migration Workbench is a migration technology tool with a standard look and feel regardless of the database being migrated.

MS SQL Server and Sybase General Issues
Does the Migration Workbench support MS SQL Server 6.0? If not, are there any plans to support this version of MS SQL Server?
No, the Migration Workbench does not support MS SQL Server 6.0. The Capture Wizard checks the version of the source database to ensure that it is MS SQL Server 6.5.
Can I migrate from MS SQL Server or Sybase to an Oracle7 database using the Migration Workbench?
This is not a supported configuration. However, it is possible to migrate your MS SQL Server or Sybase database to Oracle7 if none of your tables contain multiple text or binary columns. Before capturing the source database, you should modify the data type mappings to map TEXT to VARCHAR2 if the size of the text is less than 4000 characters, or to LONG if the text is greater than 4000 characters. You should also modify IMAGE, BINARY, and VARBINARY to LONG RAW. Please note that it is only possible to have one LONG or LONG RAW column in an an Oracle table. Therefore, your source table must only contain one of these data types.
Why do I need to define a SQL Server or Sybase System 11 ODBC data source?
You must define a SQL Server or Sybase System 11 ODBC data source so that the Migration Workbench can capture the data dictionary and extract the data from the source database.
How are duplicate objects handled?
Duplicate object names have an underscore followed by a number attached to them.
Can I edit a stored procedure, trigger, view, or check constraint in the Source Model?
Yes. You can edit the text for any of the above schema objects and then parse them using the right mouse button or the Parse item in the Object Menu.
Does the Migration Workbench support object names with spaces in their names?
No, the Migration Workbench does not support migration of objects with spaces in their names.
Does the Migration Workbench support the use of square brackets in syntax for object names such as tables and columns?
No. This is illustrated in the following example:
select [id] from [sa].[authors list]
Does the Migration Workbench support milliseconds in MS SQL Server and Sybase?
The Migration Workbench currently migrates DATE columns in MS SQL Server and Sybase to full DATE columns in Oracle. That is, YYYY-MM-DD HH24:MI:SS. It is necessary to explicitly move the data in this way to ensure that precision is maintained. Milli-second precision is lost as this is not available in Oracle. This is Year 2000-compliant. When the end-user application accesses the migrated DATE column in Oracle, you should ensure that it accesses the correct format. You can do this by setting up the init.ora 'nls_date_format' parameter or executing an 'alter session set nls_date_format = '<date_format>' ' command.

MS Access General Issues
Can the Migration Workbench migrate from MS Access 2000 to Oracle?
No, MS Access 2000 is not supported by the Migration Workbench. However, MS Access 2000 provides a capability to save your database in MS Access 97 format as long as your database does not contain any MS Access 2000-specific features. You can then migrate your MS Access 97 database to Oracle.
Can I migrate my MS Access security settings to Oracle?
The Migration Workbench does not currently support the migration of MS Access security settings such as username and password. By default, the Migration Workbench uses the name of the MS Access .mdb file as the username for the destination Oracle user. The password for an Oracle user created in this way is always Oracle.
Can the Migration Workbench migrate multiple MS Access databases to a single Oracle user?
In order to migrate multiple MS Access databases to a single user in Oracle, all of your MS Access databases must have the same name. The reason for this is that the Migration Workbench uses the name of the MS Access .mdb file as the username for the destination Oracle user. When renaming your MS Access databases, ensure that they reside in different directories.
Having renamed your MS Access databases, you must now use the Capture Wizard to add these databases for a simulataneous migration. Once the MS Access databases have been captured, they will appear individually in the Source Model with the same name. You can identify each database by viewing its path in the Source Model property sheet. When you view the Oracle Model, you will see that there is only one user. This user contains all schema objects from the multiple Source Model databases.
This approach also works for attached tables. Specifically, if you have an MS Access application database called a.mdb that contains linked tables to another MS Access database called b.mdb, you should follow the steps outlined below:

Rename both databases to the name of the Oracle user to which you want to migrate, for example employee.mdb.

Open the employee.mdb file that was originally called a.mdb and refresh the links using Tools Menu -> Add-Ins -> Linked Table Manager.

In the Capture Wizard of the Migration Workbench, add the employee.mdb file that was originally called a.mdb and proceed with the migration.

Please note that if any schema objects of the same type have the same name in multiple MS Access databases, for example, if multiple databases contain a table called <tablename>, the Migration Workbench automatically detects and resolves this naming conflict so that these objects can be successfully migrated to Oracle.
Why must I define an MS Access ODBC data source?
You must define an MS Access ODBC data source so that the Migration Workbench can extract the data from the source database.
What version of the MS Access ODBC driver should I use?
It is recommended that you use the latest 3.5 release of MS Access ODBC driver that is available from the Microsoft Web site at http://www.microsoft.com.
Why must I define an Oracle ODBC data source when migrating from MS Access?
You must define an Oracle ODBC data source if you wish to modify your MS Access database so that you can continue using your MS Access forms and reports after your data has been migrated to an Oracle database.
What happens if I receive the message "Data not found" when I enter the MS Access ODBC data source in the Capture Wizard?
If this message appears, you should reconfigure your MS Access ODBC driver in the Control Panel by selecting a valid database.
What should I do if the Migration Workbench does not respond during the capture phase of the migration?
If this happens, follow the steps outlined below:
1. Shut down the Migration Workbench.
2. Execute the following statement from the command prompt in the %ORACLE_HOME%\Omwb\bin directory.
daoexe.exe small.mdb
Why do my hyperlinks not work when my database has been migrated to Oracle?
Oracle does not support the concept of hyperlinks. Therefore, the Migration Workbench simply brings over the raw contents of the column.
What does it mean if I receive the message "No primary key defined on <tablename>; you will be unable to update records after migration."?
Jet requires a primary key on tables in Oracle in order to support dynasets against those tables. If a remote table does not have a primary key, Jet only opens a non-updateable snapshot on the table. It is possible to define updateable tables in MS Access that do not have a primary key. When these tables are migrated to Oracle, they do not have a primary key and MS Access is unable to update them. If you need the migrated tables to be updateable, you should ensure that either all updateable MS Access tables have primary keys before you migrate or that you define a primary key once the tables are migrated to Oracle. Any updateable tables that do not have primary keys are flagged with a warning in the Log Window.
What should I do if I encounter the error "JET/DAO Error 3050: Couldn't lock file"?
When DAO opens an MS Access database, it automatically generates a .ldb file. This error indicates that the .ldb file is read-only and cannot be updated. To overcome this error, you should make the .ldb file writeable by altering the properties of the file and then reselect the MS Access database for migration.
What is the meaning of error "ORA-01400: cannot insert null"?
This error occurs when some of the data in your MS Access table does not satisfy the data integrity constraints defined on that table. This implies that the data integrity constraints were defined after some or all of the data was entered into the table.

Data Types

Click here for general information about data types.
Click here for information about MS SQL Server and Sybase data types.

How does the Migration Workbench handle non-Oracle data types?
Non-Oracle data types are mapped to Oracle data types that encapsulate similar type characteristics. The data type mappings can be viewed and edited in the following locations in the Migration Workbench:

Tools Menu -> Options -> Options dialog box

Capture Wizard

Once tables have been created in the Oracle Model, you can edit the column types in the General page of the property sheet for the required table.
Can I change the data type of a single column?
Once tables have been created in the Oracle Model, you can modify the column types in either of the following places:

General page of the property sheet for the required table

Tools menu -> Options -> Options dialog box.

The Migration Workbench automatically prompts you to re-create the Oracle Model when you modify the data type mappings. This is necessary for your changes to take effect.
How are TIMESTAMP data-types migrated?
TIMESTAMP columns are migrated by creating a NUMBER column on which an UPDATE/INSERT trigger is defined. A SEQUENCE is also defined from which the trigger retrieves the next value whenever it fires.

MS SQL Server and Sybase Data Types
What is the MS SQL Server/Sybase DATETIME data type mapped to?
The MS SQL Server/Sybase DATETIME data type is mapped to the DATE data type in Oracle. The DATE data type in Oracle does not support milliseconds. This can result in a loss of precision. Oracle will support the DATETIME data type in a future release of the database.
How does the Migration Workbench handle MS SQL Server 7.0 VARCHAR data types greater than 4000?
The Migration Workbench converts any column that contains a VARCHAR field, with a length greater than 4000, to a CLOB column in Oracle. CLOB columns are a very efficient method of storing big text columns.

Data Migration

Click here for general information about data migration.
Click here for information about MS SQL Server and Sybase data migration.
Click here for information about MS Access data migration.

Can I migrate single table data?
Yes. This means that you can select a table in the Migration Workbench and migrate the data for that table only. You can do this via the Migrate Table Data menu item in the Object Menu or via the right mouse button. Please note that this menu item is only enabled once you have used the Migration Wizard to create the users and tables in the destination Oracle database.
How do I know what errors occurred while loading the table data?
All rows should load correctly. However, any failures that may occur are recorded in the Log Window.
Are constraints disabled when loading the data?
By default, the Migration Wizard will create the users and their tables first, then load the data, and finally create all constraints to avoid any problems. It is possible to do the migration in stages such that you create the users and their tables first, then create the constraints, and finally load the data. However, this may cause problems due to referential integrity so the default implementation is recommended.
Does the Migration Workbench support offline data loading?
Click here for information about the performance of data loading when migrating from MS SQL Server or Sybase.
Click here for information about the performance of data loading when migrating from MS Access.

Can I modify the SQL*Loader control files for loading the data?
There are no SQL*Loader control files used when the data is loaded from the source database to the destination Oracle database. However, you can use the Generate SQL*Loader Scripts menu item from the Object Menu to create the SQL*Loader control files in the %ORACLE_HOME%\Omwb\log directory. The Migration Workbench uses JDBC-ODBC to read the data from the source database and JDBC-OCI8 to load the data into the destination Oracle database.
What should I do if I encounter the error "ORA-22866: default character set is of varying width" when migrating to an Oracle database created with the UTF8 character set?
You can overcome this problem by migrating to an Oracle8i release 8.1.5 database and setting the init<ORACLE_SID>.ora 'compatible' parameter as follows:
compatible=8.1.0.
What is the meaning of error "ORA-01426: numeric overflow" in the Log Window?
This error means that a double value in the source database is outside the allowable range for Oracle. In this case, the Migration Workbench migrates all other columns in the row as normal and migrates a null value for the column that contains the double value. The error message in the Log Window indicates the table and the row number affected.

MS SQL Server and Sybase Data Migration

Does the Migration Workbench support offline data loading?
The Migration Workbench uses MS SQL Server or Sybase BCP in conjunction with SQL*Loader to provide an offline data loading capability for large tables. You can avail of this capability by using the Generate SQL*Loader Scripts menu item in the Object Menu. For illustration purposes, this section refers to the MS SQL Server 6.5 Plugin.
The base directory for the SQL*Loader script output for MS SQL Server 6.5 is <Log Directory>\SQLServer6. <Log Directory> is the location specified in the Logging page of the Options dialog box. The default Log directory is %ORACLE_HOME%\Omwb\log. The Migration Workbench creates a directory under this base directory that represents the date and time at which the SQL*Loader scripts were generated. For example, a sub-directory called 1-10-1999_17-58-16 indicates that the scripts were generated at 17:58 p.m. on 1st October, 1999.

1. A BCP extraction script called bcp_extract.bat is created in the <Log Directory>\SQLServer6\1-10-1999_17-58-16 directory.

2. As part of the Generate SQL*Loader Script command, a sub-directory called Oracle is created in the <Log Directory>\SQLServer6\1-10-1999_17-58-16 directory. SQL*Loader control files and a SQL*Loader script called sql_load_script.bat are placed in this Oracle directory. The SQL*Loader control files expect the data files that are created to be located in this directory also. Therefore, you should copy the bcp_extract.bat file into the Oracle directory before you execute it.

3. Run bcp_extract.bat to generate the data files. This extracts the data from the table(s) into data files called <table name>.dat.

4. The destination Oracle database does not necessarily reside on the same computer as the Migration Workbench. In this case, you should FTP the entire <Log Directory>\SQLServer6\1-10-1999_17-58-16\Oracle directory to the computer on which the destination Oracle database resides.

5. Execute the sql_load_script.bat file to insert the data from the data files into the equivalent Oracle tables.

Note: The migration of BLOBs is not supported by the scripted data move. The \n character may be removed if it exists in CLOBs.
Are there any guidelines for how long it takes to convert high volumes of data from MS SQL Server or Sybase to Oracle using the Migration Workbench?
Performance tests have not been carried out on the data migration process of the Migration Workbench. However, it is known that your setup affects performance. For example, are Oracle and MS SQL Server or Sybase located on the same server? If not, you must take the network bandwidth into account. Presently, data migration involves reading the data from the source MS SQL Server or Sybase database via a JDBC/ODBC bridge and writing that data to Oracle via JDBC. It is a straightforward data pump. However, the Migration Workbench does not take advantage of array inserts and 'commit points' to improve performance.

MS Access Data Migration
When should I modify the MS Access database?
Modifying the MS Access database involves renaming the local tables. However, during the data move phase of the migration, the Migration Workbench has a reference to the original table names and expects these names to exist in the MS Access database. Therefore, you should only modify the MS Access database after you have successfully migrated your data to Oracle.
See the Oracle Migration Workbench for MS Access Reference Guide for more information in this regard.

Does the Migration Workbench support offline data loading?
The Migration Workbench uses SQL*Loader to provide an offline data loading capability to improve the migration of large tables from MS Access. However, you must manually create the data files from within the MS Access Integrated Development Environment (IDE) as part of this process. This is explained in the steps below.
Note: MS Access 97 only supports the creation of a data file on a table-by-table basis. Therefore, you must perform the following sequence of steps
for each MS Access table individually.
The base directory for the SQL*Loader script output for MS Access is <Log Directory>\MSAccess. <Log Directory> is the location specified in the Logging page of the Options dialog box. The default Log directory is %ORACLE_HOME%\Omwb\log. The Migration Workbench creates a directory under this base directory that represents the date and time at which the SQL*Loader scripts were generated. For example, a sub-directory called 1-10-1999_17-58-16 indicates that the scripts were generated at 17:58 p.m. on 1st October, 1999.

1. Open the MS Access database in the MS Access environment.

2. Select the table whose data you wish to export to a data file.

3. Select the 'Save As/Export...' menu item from the File Menu. This launches the 'Save As...' dialog.

4. Choose the 'To an External File or Database' radio button. This launches the 'Save Table <table name> In...' dialog.

5. Choose 'Text Files' from the 'Save as type:' drop-down list. Make sure that the 'Save Formatted' check box is unchecked.

6. Click 'Export'. This launches the Export Text Wizard.

7. Select the 'Delimited' radio button and click 'Next'.

8. Check the 'Other' radio button and insert the '§' as the delimiter that will be used to separate your fields. This is the character used by the SQL*Loader control file that is generated by the Migration Workbench. You can enter the '§' symbol by using the ALT+0167 keystroke.

9. Select '{none}' from the 'Text Qualifier' drop-down list.

10. Click 'Next'.

11. On the final page of the Export Text Wizard, you are prompted for the location and name of the data file to which the table data will be exported. In the 'Export to File:' field, type the name and destination of the data file to which the table data will be exported. This location should be the <Log Directory>\MSAccess\<time>\Oracle directory.
Note: Make sure that the name of the file is identical to the name of the table and that the extension specified for the data file is .dat. This is the naming convention used by the SQL*Loader control file generated by the Migration Workbench.

12. Click 'Finish' to generate the data file.

13. Now that you have manually created the data files, run the sql_load_script.bat file in the <Log Directory>\MSAccess\<time>\Oracle directory.

Migration Wizard

Why does the creation of views, triggers, and packages sometimes fail when migrating from MS SQL Server and Sybase?
Although the Migration Workbench stores dependency information for views, triggers, and packages, this information is not used during their creation and this can result in these objects being created in the wrong order. Re-running the Migration Wizard will alleviate this problem to some degree.

National Language Support (NLS)

Does the Migration Workbench provide NLS support?
Any data or schema object names containing non-English characters may not be migrated by the Migration Workbench. This issue will be addressed in a future release.

Parsing

MS SQL Server and Sybase Parsing

What is the equivalent of the MS SQL Server or Sybase SYSOBJECTS table in Oracle?
The ALL_OBJECTS table in an Oracle database contains information about all objects that reside in the database. Other references to SQL Server data dictionary tables have to be manually converted to the Oracle equivalent.
Why do I get the error "Too many declarations of 'TO_CHAR' match this call"?
'TO_CHAR' on a CHAR or VARCHAR2 causes this error on compilation. You can remove the TO_CHAR call in this case.
How are MS SQL Server and Sybase EXEC statements parsed?
SQL statements such as EXEC ("exec_string") are assumed to be dynamic SQL and are sent to an EXEC_SQL PL/SQL stored procedure for execution. Since the contents of the string, that is "exec_string", are not parsed, manual intervention may be required. Other EXEC statements such as EXEC (exec_string) are converted to PL/SQL procedure calls.
Can I use the LIKE clause in SQL statements?
LIKE can be used in most instances as long as UNIX-style regular expressions such as square brackets are not used. However, it is not possible to use the following construct outside a SQL statement:
IF (expression) LIKE (expression)
Does Oracle support full UNIX-style regular expression handling?
No.
Are DUMP TRANSACTION and DROP INDEX SQL commands supported?
LOAD and DUMP commands cause parse errors. Most MS SQL Server and Sybase DDL commands are ignored and a warning is written to the Log Window.

Temporary Tables

Click here for general information about temporary tables.
Click here for information about MS SQL Server and Sybase temporary tables.

How are temporary tables handled in Oracle8?
The Migration Workbench uses the temporary tables feature of Oracle8i by default. If you are migrating to an Oracle8 Server release 8.0.5 database, you should change the parse options for stored procedures, triggers, and views. To do this, deselect the Generate Oracle8i temp tables check box in the Parse Options page of the property sheets for these schema objects. When the parser encounters SQL statements such as CREATE #TABLE in a stored procedure, a temporary table is explicitly created. When the parser encounters SQL statements such as INSERT INTO #TABLE in a trigger or stored procedure, a temporary table is implicitly created. Implicit creation is required when a CREATE TABLE statement cannot be found.
These creation (DDL) statements are associated with stored procedures and triggers and are executed by the Migration Wizard prior to the creation of the stored procedures or triggers. Clashes can arise when the parser creates multiple copies of the same DDL statement. Any clashes are written to the Log Window and these can be ignored.
Please note that in order for the database to use the temporary tables feature of Oracle8i, you should set the init<ORACLE_SID>.ora 'compatible' parameter as follows: compatible=8.1.0.

MS SQL Server and Sybase Temporary Tables
Why does a SELECT on a temporary table fail when I have migrated a stored procedure to an Oracle database?
SELECT statements for temporary tables (#TABLES) may fail when a SESSID column is added. For example, the following SQL statement
select SESSID, * from a_table
does not work in an Oracle database. However, the following SQL statement does work in Oracle:
select SESSID, tablea.* from a_table

Result Sets

MS SQL Server and Sybase Result Sets
Does the Migration Workbench support the conversion of SQL Server stored procedures that return multiple result sets?
The Migration Workbench creates a single cursor variable in an OUT argument in Oracle and uses that variable for all the converted result set SELECT statements. This causes only the results of the last SELECT statement that was opened to be returned in the cursor variable. The results of all other SELECT statements are lost. The workaround for this is to create a separate cursor variable argument for each result set SELECT statement and to open each cursor variable using a separate SELECT statement.
Does the Migration Workbench use weak REF CURSORS?
The Migration Workbench currently uses packages and strong REF CURSORS. However, it is intended to use weak REF CURSORS in a future release. This means that packages will no longer need to be created.
How are result sets/dynasets returned to the calling program?
The Migration Workbench parser adds an extra argument of type REF CURSOR for result sets/dynasets. This type is understood and can be manipulated by both PL/SQL and Oracle JDBC. Oracle8 release 8.0.5 ODBC drivers support REF CURSORs. This means that the additional argument must be explicitly handled by the application and the client application code must be changed. However, some third-party vendors such as Intersolv supply ODBC drivers for Oracle that support REF CURSORs and can implicitly make use of REF CURSORs for using result sets/dynasets. Therefore, no change is required in the client application code. The Oracle8i ODBC Driver release 8.1.5.4.0 provides this functionality also. This is illustrated in the following examples of an MS SQL Server/Sybase stored procedure and its equivalent Oracle package and stored procedure as generated by the Migration Workbench parser.
MS SQL Server/Sybase Stored Procedure
CREATE PROCEDURE byroyalty
AS
select au_id from titleauthor
GO
Oracle8iPackage and Stored Procedure
PACKAGE BYROYALTYPkg AS
TYPE RT1 IS RECORD (
  au_id   titleauthor.au_id%TYPE
  );
TYPE RCT1 IS REF CURSOR RETURN RT1;
END;
PROCEDURE byroyalty(
RC1  IN OUT byroyaltyPkg.RCT1)
AS
StoO_selcnt INTEGER;
StoO_error  INTEGER;
StoO_rowcnt INTEGER;
StoO_errmsg VARCHAR2(255);
StoO_sqlstatus INTEGER;
BEGIN
 OPEN RC1 FOR
 SELECT au_id FROM titleauthor;
END byroyalty;
The following example illustrates the typical ODBC code used by Intersolv to call the above MS SQL Server/Sybase stored procedure. This code also works for the above Oracle8i package and stored procedure. Note that error handling must be added in a real application:
     SQLPrepare(...,'{call byroyalty()}',...)
     SQLExecute()
     SQLBindCol()
     SQLFetch()
Comments:
SQLPrepare(...,'{call byroyalty()}',...) is the ODBC SQL syntax used to execute stored procedures.
SQLExecute()executes the stored procedure.
SQLBindCol()assigns storage for result column 1 in the result set (au_id).
SQLFetch() fetches the first record from the result set generated by the stored procedure.
The following examples illustrate how to call the above MS SQL Server/Sybase stored procedure with result sets/dynasets in Visual Basic using DAO and RDO on top of ODBC. This code works for Oracle8i packages and stored procedures if you use an Intersolv ODBC driver or the Oracle8i ODBC driver release 8.1.5.4.0 to understand Oracle REF CURSORs.
DAO
Private Sub Command2_Click()
Dim sSql As String
    sSql = "{call byroyalty()}"
    'In Oracle ODBC driver use refcusor argument to get result set
    Set rCustomers = dbsServer.OpenRecordset(sSql, dbOpenDynamic)
    Text4 = rCustomers.Fields(0)
theend:
End Sub
Please note, this example assumes that a DAO connection has been set up already.
RDO
Private Sub Command1_Click()
    StrSql = "{call byroyalty}"
    'in oracle odbc driver uses refcusor argument to get result set
    Set Ps = connx1.CreatePreparedStatement("PsTest", StrSql)
    Set Rs = Ps.OpenResultSet(rdOpenStatic)
    Text3 = Rs!au_id
    Rs.Close
End Sub
Please note, this example assumes that an RDO connection has been set up already.

Stored Procedures

MS SQL Server and Sybase Stored Procedures
Can I use the Migration Workbench to migrate stored procedures only?
The Migration Workbench works first by capturing the source database. This involves loading the entire data dictionary of the source database into the Source Model and creating its Oracle equivalent called the Oracle Model. Once the capture is complete, you can run the Migration Wizard. This forces you to create all users and their tables. Once the users and tables have been created, you can create the stored procedures.
If you want to stop the Migration Wizard forcing you to create the users and tables it is possible to "fool" the Migration Workbench into updating the Oracle Migration Workbench Repository. To do this, execute the following SQL statement from within your repository:
SQL> update MTG_PROJECTS set STATE = 3;
How are DDL commands handled?
The parser does not handle some DDL commands in stored procedures. For example, DROP INDEX, CREATE VIEW, DROP VIEW, GRANT, DROP PROCEDURE, and CREATE INDEX.
Can I parse a specific stored procedure?
Yes. You can do this via the Parse menu item in the Object Menu or via the right mouse button when you have selected a particular stored procedure.
Do cross-database procedure calls require manual intervention?
MS SQL Server and Sybase databases are mapped to tablespaces instead of to users. Therefore, procedures in one database that call procedures in another database must be edited after migration. For example, there are two databases called HR and FINANCE, owned by the user SA. There is a procedure called get_sales_employees in the HR database and a procedure called get_quarter_bonuses in the FINANCE database. If get_quarter_bonuses makes a call to get_sales_employees, the syntax will be as follows:
hr..get_sales_employees
The Migration Workbench maps individual databases to separate tablespaces and creates both stored procedures (get_sales_employees and get_quarter_bonuses) under the user SA. Therefore, the above statement is converted to the following:
hr.get_sales_employees
In Oracle, this would require that the get_sales_employees be owned by a user called HR. However, since there is no user called HR in the converted Oracle database, this statement fails after conversion. This problem currently requires manual intervention before migration. You must create users called HR and FINANCE in the MS SQL Server database and ensure that HR owns the get_sales_employees procedure and FINANCE owns the get_quarter_bonuses procedure. This prevents the converted statement from failing after conversion.
See the Parsing section for additional information about parsing.
How are MS SQL Server outer joins migrated to Oracle outer joins by the Migration Workbench?
Since the rough algorithm used in the Migration Workbench is not comprehensive, a warning is added to notify you that complex outer joins may not be converted correctly.
The Migration Workbench converts a SQL Server inner join to an Oracle join as illustrated in the following code example:
select * from a inner join b on a.col1=b.col2;
select * from a , b where a.col1=b.col2;
The Migration Workbench converts left joins and right joins in SQL Server to Oracle as indicated below:
select * from a left join b on a.col1=b.col2;
remember the table to the right of left join and add (+) to it's columns.
select * from a, b where a.col1=b.col2(+);
select * from a right join b on a.col1=b.col2;
remember the table to the left of right join and add (+) to it's columns.
select * from a, b where a.col1(+)=b.col2;
The Migration Workbench does not convert full outer joins to Oracle.

How can I convert full outer joins from SQL Server to Oracle?
There are several ways to express a full outer join within Oracle. For example, in the following query the predicate a.col1 (+) = b.col1 (+) is pseudo-Oracle notation for a full outer join although this predicate is not currently supported in Oracle:
select a.col2 acol2, a.col1 acol1, b.col1 bcol1, b.col2 bcol2
from a,b where a.col1 (+) = b.col1 (+);
The most efficient way of executing this query is to use a UNION ALL of a left outer join and a right outer join, with an additional predicate, as illustrated below.
select a.col2 acol2, a.col1 acol1, b.col1 bcol1, b.col2 bcol2
from a,b where a.col1=b.col1(+)
union all
select a.col2 acol2, a.col1 acol1, b.col1 bcol1, b.col2 bcol2
from a,b where a.col1(+)=b.col1 and a.col1 is null;
How does the Migration Workbench migrate MS SQL Server outer joins to ODBC escape sequences?
The following example illustrates how the Migration Workbench migrates MS SQL Server outer joins to ODBC escape sequences. The Oracle ODBC Driver release 8.1.5.3.0 and later releases support ODBC escape sequences.
Original SQL Server Statement:
SELECT A.A,B.B
FROM A LEFT OUTER JOIN B ON A.A =  B.B
WHERE ((A.C=3));
ODBC Statement:
SELECT A.A,B.B
FROM {oj A LEFT OUTER JOIN B ON A.A =  B.B }
WHERE ((A.C=3));

Global Variables

MS SQL Server and Sybase Global Variable Issues
Does the Migration Workbench support global variables?
Currently, the Migration Workbench provides limited support for global variables. @@FETCH_STATUS, @@SQLSTATUS, @@ROWCOUNT, @@ERROR, AND @@VERSION are supported. To continue parsing, other global variables are treated as normal variables and the following warning is added:
[SPCONV-ERR (<NN>)]: Parsing [(<NAME>)] ('<STRING>': Global Variable treated as variable
How does the Migration Workbench handle the global variable @@SERVERNAME?
@@SERVERNAME is treated as a normal variable. The Oracle equivalent may be based on the name of the database to which you are connected.
How does the Migration Workbench handle the global variable @@IDENTITY?
@@IDENTITY is treated as a normal variable. The current IDENTITY value may need to be retrieved from the column in the appropriate table. The IDENTITY column is replaced by a sequence and a trigger in Oracle.
How are IDENTITY columns mapped to Oracle?
A NUMBER column with an associated sequence and trigger is created in the Oracle database for each IDENTITY column in the MS SQL Server or Sybase database. Each time a row is inserted, the trigger queries the sequence for the next value and inserts that value into the IDENTITY column.
How does the Migration Workbench handle the global variable @@SPID?
@@SPID is treated as a normal variable. The Oracle equivalent may be based on the v$sessions table. Note that a user might not have a dedicated server process in Oracle.
How does the Migration Workbench handle the global variable @@TRANSTATE?
@@TRANSTATE is treated as a normal variable. There is no Oracle equivalent. Transactions in Oracle will either complete or fail. Errors in Oracle are handled via exceptions and SQLCODE. Refer to the PL/SQL User's Guide and Reference, Release 8.1.5 (Part Number: A67842-01) for more information in this regard.

Triggers

MS SQL Server and Sybase Trigger Issues

Are there any issues with the use of deleted and inserted pseudo columns from MS SQL Server?
The parser in the MS SQL Server 7.0 Plugin incorrectly converts MS SQL Server T-SQL stored procedures that contain
SELECT COL1, COL2 FROM DELETED
to the following in PL/SQL:
SELECT COL1, COL2 FROM DUAL
You must manually correct PL/SQL stored procedures that are converted in this way as outlined in the following example:
SELECT :OLD.COL1 :OLD.COL2 FROM DUAL
Where COL1 and COL2 are columns of the table DELETED.
See the Parsing section for additional information about parsing.

Views

Some views may not be migrated due to issues with the parser.

Packages

MS SQL Server and Sybase Packages
Why do I see * in the packages produced by the parser?
Where name resolution cannot be performed by the parser, * appears in package definitions. This must be replaced by the relevant name and type definitions.
See the Parsing section for additional information about parsing.

Users

MS SQL Server and Sybase Users
How can I migrate roles and privileges (under the 'public' role) from MS SQL Server or Sybase to Oracle using the Migration Workbench? (All objects belong to the 'sa' schema in MS SQL Server and Sybase).
Roles and privileges are handled automatically. Every MS SQL Server or Sybase user is mapped to its associated login and it is the login that is created in the Oracle database. The Migration Workbench automatically creates Oracle roles for each MS SQL Server or Sybase group and then assigns that role to each MS SQL Server or Sybase login. That is, a user is created in Oracle for the login, a role is created for the group, and the user is assigned the role.
Every privilege that an MS SQL Server or Sybase user has is also given to its equivalent Oracle user. If loginA is associated with userA in the PUBS database, loginB is associated with userB in the PUBS database, and userA owns a table EMP and grants userB select permission on that table, the Migration Workbench creates two users called loginA and loginB in Oracle. LoginA owns the table EMP and loginB is granted select access on the table. The Migration Workbench also creates private synonyms for every object in another schema on which a user has some privilege. In this example, loginB would have a private synonym called EMP. This synonym is really loginA.emp.

Tables

How do I customize the storage options for a table?
The storage options for tables and indexes can be modified before running the Migration Wizard. In the Oracle Model, select the Tables container and go to the Default Storage Options tab of the Tables property sheet. Make your modifications and click Apply. This will set the options for all tables in the Oracle Model. If you would like to modify the default creation options for a particular table, expand the Tables container in the Navigator Pane, select the table you wish to modify, and make your modifications in the Override Default Creation Options tab of the property sheet for that table.
Do table defaults require editing?
Table defaults containing dates and bit-wise operators require manual editing after migration to Oracle.

Rules

MS SQL Server and Sybase Rules
How are rules handled?
Rules are migrated as table check constraints. This means that every table column that has a rule associated with it will have a check constraint in the destination Oracle database. Patterns such as LIKE[a-y] are translated using TRANSLATE.
How are rules with getDate() function converted?
Rules are converted into equivalent Oracle check constraints. The getdate() function is semantically equivalent to SYSDATE in Oracle. However, Oracle does not support the use of SYSDATE in check constraint definitions. Therefore, a database trigger must be created in Oracle to implement rules that use the getdate() function.

User-Defined Types

MS SQL Server and Sybase User-Defined Types
How are MS SQL Server and Sybase user-defined types handled?
The Migration Workbench maps MS SQL Server and Sybase user-defined types to their base types.

Foreign Keys

What happens if I receive error "ORA-02298: parent key not found"?
Oracle requires that the column specified in the reference table be either a primary key or unique index.

Client Applications

How do client applications access the data that has been migrated from the source database to Oracle?
JDBC, Oracle ODBC drivers, Oracle OLEDB, and third-party ODBC and OLEDB drivers call all be used to access client applications after the source database has been migrated to Oracle. Oracle recommends using OO4O instead of ADO. Most client applications seem to use Visual Basic and/or C++. Other client-side tools such as PowerBuilder, Delphi, or ASP should work if they go through ODBC or OLEDB, although these have not been tested by the Migration Workbench development team.
The following documents provide additional information about how to extend your client application:

Oracle Objects for OLE and OLEDB/ADO Cookbook

Using the Oracle ODBC Drivers with Third-Party Products

You can find these documents on the Oracle Technology Network (OTN) Web site at http://technet.oracle.com/tech/migration/

Error Handling and Support

What release of Oracle8 is supported?
Oracle8 release 8.0.5, Oracle8i release 8.1.5, and Oracle8i release 2 (8.1.6) are supported.
What platforms does Oracle Migration Workbench run on?
The Migration Workbench runs on Windows NT 4.0 (Service Pack 5) and Windows 95/98.
How do I know what errors occured when running the Migration Wizard?
All errors are logged to the Log Window and the columns of the Log Window can be sorted by clicking on the column header. During the creation phase of the migration, the Migration Wizard creates three log files that record all successful CREATE and ALTER statements, the equivalent DROP and ALTER statements, and all SQL statements that the Migration Workbench was unable to execute. The location of these log files is governed by the Log File Directory setting in the Logging page of the Options dialog. The default location is
%ORACLE_HOME%\omwb\log
If the Migration Workbench fails to respond, check the contents of the Error.log file to see if there are any errors recorded. This file is located in
%ORACLE_HOME%\Omwb\log\Error.log
The contents of this file can be sent to the Migration Workbench development team at infomwb@ie.oracle.com.
Are there any e-mail addresses to which I can send my queries?
You should send any queries by e-mail to infomwb@ie.oracle.com.
You can also access the discussion forum for the Migration Workbench on the Oracle Technology Network (OTN) Web site at http://technet.oracle.com/tech/migration/workbench/index2.htm?Support&htdocs/discussion.htm

Y2K

Is the Migration Workbench Year 2000-compliant?
If you migrate a database that is not Year 2000-compliant, it will not be Year 2000-compliant after it is migrated. For example, if a two-digit column is used to represent the year in a table, this will be migrated to a two-digit column in Oracle. There is no way of interpreting non-DATE type columns to see if they are being used as DATE columns.

1.	A BCP extraction script called bcp_extract.bat is created in the <Log Directory>\SQLServer6\1-10-1999_17-58-16 directory.
2.	As part of the Generate SQLLoader Script command, a sub-directory called Oracle is created in the <Log Directory>\SQLServer6\1-10-1999_17-58-16 directory. SQLLoader control files and a SQLLoader script called sql_load_script.bat* are placed in this Oracle directory. The SQLLoader control files expect the data files that are created to be located in this directory also. Therefore, you should copy the bcp_extract.bat* file into the Oracle directory before you execute it.
3.	Run bcp_extract.bat to generate the data files. This extracts the data from the table(s) into data files called *<table name>.dat*.
4.	The destination Oracle database does not necessarily reside on the same computer as the Migration Workbench. In this case, you should FTP the entire <Log Directory>\SQLServer6\1-10-1999_17-58-16\Oracle directory to the computer on which the destination Oracle database resides.
5.	Execute the sql_load_script.bat file to insert the data from the data files into the equivalent Oracle tables.

1.	Open the MS Access database in the MS Access environment.
2.	Select the table whose data you wish to export to a data file.
3.	Select the 'Save As/Export...' menu item from the File Menu. This launches the 'Save As...' dialog.
4.	Choose the 'To an External File or Database' radio button. This launches the 'Save Table <table name> In...' dialog.
5.	Choose 'Text Files' from the 'Save as type:' drop-down list. Make sure that the 'Save Formatted' check box is unchecked.
6.	Click 'Export'. This launches the Export Text Wizard.
7.	Select the 'Delimited' radio button and click 'Next'.
8.	Check the 'Other' radio button and insert the '§' as the delimiter that will be used to separate your fields. This is the character used by the SQL*Loader control file that is generated by the Migration Workbench. You can enter the '§' symbol by using the ALT+0167 keystroke.
9.	Select '{none}' from the 'Text Qualifier' drop-down list.
10.	Click 'Next'.
11.	On the final page of the Export Text Wizard, you are prompted for the location and name of the data file to which the table data will be exported. In the 'Export to File:' field, type the name and destination of the data file to which the table data will be exported. This location should be the <Log Directory>\MSAccess\<time>\Oracle directory. Note: Make sure that the name of the file is identical to the name of the table and that the extension specified for the data file is .dat. This is the naming convention used by the SQL*Loader control file generated by the Migration Workbench.
12.	Click 'Finish' to generate the data file.
13.	Now that you have manually created the data files, run the sql_load_script.bat file in the <Log Directory>\MSAccess\<time>\Oracle directory.