|Oracle Migration Workbench Frequently Asked Questions (FAQ)
Release 9.2.0 for Microsoft Windows 98/2000 and Microsoft Windows NT
Part Number A97247-01
This chapter contains frequently asked questions about using the Oracle Migration Workbench to migrate from a Microsoft Access 2.0, 95, 97, or 2000 database to an Oracle Server. It contains the following sections:
This section contains Microsoft Access pre-installation questions.
Yes. The Migration Workbench release 9.2.0 supports the migration of Microsoft Access 2000 to a destination Oracle database. In order to migrate a Microsoft Access 2000 database, you must have Microsoft Access 2000 installed on your system.
Before you can migrate a Microsoft Access database to an Oracle database, you must first export the MDB file to an XML file. Use the Oracle Migration Workbench Exporter for Microsoft Access to do this. This is located in the
The Oracle Migration Workbench Exporter extracts the schematic information from the MDB file and stores it in the XML file. The Migration Workbench then uses this XML file to capture the Microsoft Access database within the Capture Wizard.
If the source Microsoft Access database uses linked tables, the schematic information for the attached MDB files are also automatically stored in the XML file.
You can export and capture only one Microsoft Access database at a time.
This section contains Microsoft Access installation and configuration questions.
In order to migrate a Microsoft Access 97 database to an Oracle database, you must have the following software installed on the same system as the Migration Workbench:
You can obtain this by downloading the latest version of the Microsoft Data Access Components (MDAC) from the Microsoft Web site at:
You can obtain this by downloading Microsoft Access 97.
This allow Microsoft Access forms and reports to work with the migrated data. To install the Oracle ODBC driver, select the Custom Install option when installing the Migration Workbench. After you have installed the Oracle ODBC driver, you should set the Oracle ODBC Data Source.
To migrate Microsoft Access 2000 database to an Oracle database, you must have the following software installed on the same system as the Migration Workbench:
For more information, see Setting the Oracle ODBC Data Source in the Oracle Migration Workbench Online Help.
If the Microsoft Access ODBC driver is installed correctly, this issue may occur because you are logged on to a system where another user (with different user privileges) has installed the Microsoft Access Database.
To resolve this issue you must have the same privileges as the other user.
This section contains Microsoft Access data migration questions.
If you have defined a foreign key relationship between two tables outside of the MDB file that contains these tables, such as between two linked tables, the Oracle Model might hang. To avoid this problem, define the table relationship between tables within the MDB file.
If you receive the ORA-00001 error message, the Migration Workbench hangs while it loads indexes.
You might receive this error if there is a problem with the owner profile within the Migration Workbench Repository or if the administrator of the Microsoft Access database does not have full write permissions. You may also receive this error message if the PL/SQL package has not been created properly.
The ORA-00001error message can appear if the Microsoft Access database administrator does not have full permissions. To fix this problem:
If the PL/SQL package was not created correctly in the Oracle database, no triggers associated with the Microsoft Access Source Model tables work. The Source Model uses sequence and trigger pairs to generate unique IDs for some of the columns within the tables.
To check if the PL/SQL package was created properly, log in to SQL*Plus and execute the following
If all values returned by this statement are
-1, the trigger that the Migration Workbench uses to return unique table IDs was not created.
You can test if the Migration Workbench correctly created the triggers by executing the following statement:
If the following names are not returned by this statement then the triggers were never created. If this is the case, consult with the DBA and investigate why triggers were not created.
If the previous names are returned, you should verify that the triggers work. The
ACC_DB_INFO table has a sequence/trigger pair defined on the
DBID column. To verify that the triggers work:
DBIDvalue in the
ACC_DB_INFOtable to ensure that the values in the
DBIDcolumn are sequential.
If the trigger does not work, the value specified in the
INSERT statement (in our example
999) for the
DBID column is displayed.
To test whether you can manually create the sequence/trigger pair execute the following commands:
DROP SEQUENCE ACC_SEQ_DB_INCR; CREATE SEQUENCE ACC_SEQ_DB_INCR START WITH 1; CREATE OR REPLACE TRIGGER ACC_TR_DB_INCR BEFORE INSERT ON ACC_DB_INFO FOR EACH ROW BEGIN SELECT ACC_SEQ_DB_INCR.NEXTVAL INTO :NEW.DBID FROM DUAL; END; /
Consult the DBA if you receive any error messages when attempting to execute the previous commands.
After you have attempted to manually create the sequence and trigger, the following error might display:
This error indicates that there is something wrong with the way in which you set up the database. To correct this, create a new database instance using the database configuration assistant that ships as standard with Oracle9i or Oracle8i.
Prompted to associate a file type with the omwb.mde file
Incorrect version of Microsoft Access is executed
Error: ActiveX component can't create object
Error: Unable to convert or enable MDE file.
The version of Microsoft Access must be compatible with the Microsoft Access database that you want to migrate. If you receive one of the preceding error messages, you might have a version of Microsoft Access that is incompatible with the Microsoft Access database that you are migrating. If you have more than one version of Microsoft Access installed on the same system, the incorrect version of the Oracle Migration Workbench Exporter for Microsoft Access might execute.
To ensure that you use the correct version of the Oracle Migration Workbench Exporter for Microsoft Access (
omwb.mde) on the system:
omwb2000.mdefiles located in the
For validation rules, the Migration Workbench generates triggers. The Migration Workbench does not parse the
between x and y or is null syntax correctly. In order to overcome this problem, the
between x and y or is null syntax, replace the trigger text in the Oracle Model. The following table shows the incorrect syntax and the correct syntax that you should use to edit the code of the trigger.
|Incorrect Syntax||Correct Syntax|
No. The Migration Workbench does not support migrating Microsoft Access databases that have security enabled. To ensure that the Migration Workbench can migrate the Microsoft Access table data, copy the contents of the secured database into a new database. Everything is copied to the new database, except the security settings. You can then migrate the new database to an Oracle Server.
To copy the contents of the secured database into a new database:
There is a bug within Microsoft Access 95 that sometimes generates erroneous relations. Since these erroneous relations do not appear in the Microsoft Access IDE user interface, you cannot delete them by using the application because they are hidden from the user. To correct this problem, create a new copy of the Microsoft Access database with no relations specified. You can then manually re-create the correct relations in the new database.
To copy the contents of the Microsoft Access database without specifying relations:
No. The Migration Workbench does not permit migration of replica Microsoft Access databases.
Yes. To migrate multiple Microsoft Access databases to a single Oracle user, give all of the Microsoft Access databases the same name. All of the Microsoft Access databases must have the same name because the Migration Workbench uses the name of the Microsoft Access MDB file as the user name for the destination Oracle user.
Ensure that all the Microsoft Access databases with the same name reside in different directories from each other. Then, use the Capture Wizard to select the databases for a simultaneous migration.
Microsoft Access databases that have been captured in the Source Model appear individually, however, they all appear with the same name. You can identify a database by viewing its path in the Source Model property sheet. When you view the Oracle Model, you see that there is only one user. This user contains all schema objects from the multiple Source Model databases.
You can have a Microsoft Access application database that contains linked tables to another Microsoft Access database. To migrate multiple Microsoft Access databases containing attached tables to a single Oracle user:
employee.mdbfile in Microsoft Access, then choose Tools > Add-Ins >Linked Table Manager to refresh the links.
employee.mdbfile, then proceed with migration.
If schema objects of the same type have the same name in multiple Microsoft Access databases the Migration Workbench automatically detects and resolves this naming conflict.
No. Oracle Servers do not support hyperlinks. Therefore, the Migration Workbench simply brings over the raw contents of the column.
This error message occurs because the specified table does not have a primary key defined on it. Jet requires a primary key on tables in Oracle databases in order to support dynasets against those tables. If the Oracle database table does not have a primary key, Jet only opens a non-editable snapshot on the table. It is possible to define a table in Microsoft Access that you can update yet does not have a primary key. When you migrate the table to an Oracle database, it does not have a primary key; therefore, the Jet engine is unable to update it. If you want to update the contents of the table through the Jet engine, do one of the following:
When DAO opens a Microsoft 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 writable by altering the properties of the file and then reselect the Microsoft Access database for migration.
For more information on Microsoft Jet error messages, see the Microsoft Access Reference Guide.
You must define an Oracle ODBC data source to modify the Microsoft Access database. Defining an Oracle ODBC data source allows you to continue using the Microsoft Access forms and reports after you have used the Migration Workbench to migrate the data to a destination Oracle database.
To continue using the Microsoft Access front end with the data that you have migrated to an Oracle Server, the Migration Workbench must make some modifications to the Microsoft Access database.
To modify the Microsoft Access database, you must rename local tables. However, during the data move phase of the migration, the Migration Workbench references the original table names and expects these names to exist in the Microsoft Access database. Therefore, you should modify the Microsoft Access database after you have successfully migrated the data to Oracle.
Yes. The Migration Workbench uses SQL*Loader to provide an offline data loading capability, which improves the migration of large tables from Microsoft Access. To use offline data loading, you must manually create the data files from within the Microsoft Access Integrated Development Environment (IDE).
Microsoft Access 97 only supports the creation of a data file on a table-by-table basis. Therefore, you must perform the steps outlined in this section for each Microsoft Access table individually.
The base directory for the SQL*Loader script output for Microsoft Access is
%ORACLE_HOME%\Omwb\sqlloader_scripts\MSAccess\timestamp. The default Log directory is
%ORACLE_HOME%\Omwb\sqlloader_scripts\. The Migration Workbench creates a directory in this base directory that represents the date and time that you generated the SQL*Loader scripts. 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.
To manually create the data files from within the Microsoft Access Integrated Development Environment (IDE):
sql_load_script.batfile in the
The Oracle ODBC driver automatically handles both BLOB and CLOB columns. By default, all Microsoft Access Memo fields are mapped to
CLOB datatypes in an Oracle database. You can use the Microsoft Access front end with the destination Oracle database. Unlike
LONG columns, from Oracle 8.x onwards you can have any number of
LOB columns in a table. You can have either internal LOBs, meaning they are inside the database, or external LOBs, meaning they are in a file on the file system but under the database's transactional control. The advantages of LOBs are that you can replicate them or index them using the context option.
This error is associated with the internal ODBC DSN which is installed with the Migration Workbench. The most likely cause is that the Microsoft Access plug-in has been reinstalled into a different Oracle Home or that the small.mdb file installed with the Migration Workbench has been deleted or moved.
To resolve this issue: