Oracle Migration Workbench Error

668645

I am trying to migrate and Access database 2000 to Oracle database (version 10.2.0.3) with Oracle Migration Workbench.
Migration Workbench generated file <AccessDB_Name>.sql.
When I run the file on the Oracle database under the pre-created Oracle account I get the following error:


SQL> show user
USER is "SCO_RECCLUB"
SQL> REM This SQL script was generated by the Oracle Migration Workbench
SQL> REM Exporter for Microsoft Access on 22/10/2008 10:27:03
SQL> REM Release: 10.2.0.2.0
SQL> REM Version: Microsoft Access 2000
SQL> REM Do not modify this file as any modification will invalidate the export
SQL> REM and subsequent migration of your Microsoft Access database
SQL>
SQL> set define off
SQL> set verify off
SQL> set serveroutput on size 1000000
SQL> set feedback off
SQL> WHENEVER SQLERROR EXIT SQL.SQLCODE ROLLBACK
SQL>
SQL> begin
2
3 WWV_MIG_ACC_LOAD.INS_WWV_MIG_ACCESS (
4 p_dbid => 1,
5 p_dbname => 'rclub_be',
6 p_dbsize => '2338KB',
7 p_dbpathname => 'C:\Rec C DB\rclub_be.mdb',
8 p_dbuser => 'rclub_be',
9 p_dbpassword => ' ',
10 p_isappdb => 1,
11 p_isattacheddb => 0,
12 p_convertdb => 1,
13 p_jetversion => 4.0,
14 p_accessversion => 'MS Access 2000',
15 p_build => 211,
16 p_collatingorder => 1033,
17 p_querytimeout => 60,
18 p_startupform => NULL,
19 p_startupshowstatusbar => 0
20 );
21
22 end;
23 /
begin
*
ERROR at line 1:
ORA-02291: integrity constraint (FLOWS_030100.WWV_MIG_ACC_FK) violated - parent key not found
ORA-06512: at "FLOWS_030100.WWV_MIG_ACC_LOAD", line 120
ORA-06512: at line 3


Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

I am not sure how to proceed with the migration or if it is even possible at this stage. Any help or explanation of the error message above would be very much appreciated.

Thank you for your help

Paul

Hilary Farrell-Oracle

Hi Paul,

The <AccessDB_Name>.sql file that you refer to, which is generated by the Exporter Tool for MS Access, is required for migrating your Access application to Oracle Application Express. The Exporter Tool can generate two files:

* an XML file containing the database schema information for your MS Access database. This file is required by Oracle SQL Developer Migration Workbench, to migrate your Access database to Oracle.
* a SQL script containing the application information for your MS Access application. This file is only used by the "Application Migrations" application, within Oracle Application Express 3.0 or later releases.

I would recommend that you do the following:

1. Run the Exporter tool again, and ensure you select the "Export for Oracle SQL Developer" option. This option will result in the generation of an XML file, <AccessDB_name>.xml.
2. Run Oracle SQL Developer, and create a Migration Repository
3. Using the Migration > Capture Microsoft Access Exported XML menu option, upload the XML file generated in step 1. This will generate a Captured Model of the Access database.
4. Create an Oracle Model, by right-clicking on the Captured Model.
5. Generate the DDL statements for creating your migrated schema, by right-clicking on your Converted Model & selecting the "Generate" context menu option. This will result in the generation of a SQL script containing the DDL statements that you are required to run, in order to generate your newly migrated schema.
6. Run the SQL script containing your DDL statements, to create your new migrated schema.

I would also recommend that you review the Online documentation for [MS Access Migrations|http://download.oracle.com/docs/cd/E12151_01/doc.150/e12154/intro_access.htm#BGBIDDGI] , to gain a better understanding of the migration process. I hope this helps.

Regards,
Hilary

Hilary Farrell-Oracle

Hi Paul,

The <AccessDB_Name>.sql file that you refer to, which is generated by the Exporter Tool for MS Access, is required for migrating your Access application to Oracle Application Express. The Exporter Tool can generate two files:

* an XML file containing the database schema information for your MS Access database. This file is required by Oracle SQL Developer Migration Workbench, to migrate your Access database to Oracle.
* a SQL script containing the application information for your MS Access application. This file is only used by the "Application Migrations" application, within Oracle Application Express 3.0 or later releases.

I would recommend that you do the following:

1. Run the Exporter tool again, and ensure you select the "Export for Oracle SQL Developer" option. This option will result in the generation of an XML file, <AccessDB_name>.xml.
2. Run Oracle SQL Developer, and create a Migration Repository
3. Using the Migration > Capture Microsoft Access Exported XML menu option, upload the XML file generated in step 1. This will generate a Captured Model of the Access database.
4. Create an Oracle Model, by right-clicking on the Captured Model.
5. Generate the DDL statements for creating your migrated schema, by right-clicking on your Converted Model & selecting the "Generate" context menu option. This will result in the generation of a SQL script containing the DDL statements that you are required to run, in order to generate your newly migrated schema.
6. Run the SQL script containing your DDL statements, to create your new migrated schema.

I would also recommend that you review the Online documentation for [MS Access Migrations|http://download.oracle.com/docs/cd/E12151_01/doc.150/e12154/intro_access.htm#BGBIDDGI] , to gain a better understanding of the migration process. I hope this helps.

Regards,
Hilary

Kgronau-Oracle

Paul,
another option would be switch to SQL Developer Migration Workbench:
http://htmldb.oraclecorp.com/pls/htmldb/f?p=12639:10:4225030597214971

This product contains the latest version of the MS Access migration utility.

668645

Thanks Hilary,

You hit the nail straight on the head.. so to speak. This has worked perfectly and I am now in the process of populating the existing schema with the XML data that I migrated form the access database. If I have any problems I will post them up on this forum again.

Than you very much for your help.


Paul

Edited by: user10525373 on 05-Nov-2008 07:34