2 Replies Latest reply on Jan 15, 2013 4:19 PM by user34885

    MS Access to APEX Migration Errors/Problems NEED ASSISTANCE

      I have tried to perform a MS Access to APEX Migration. There are 2 ways in which this can be done. I am having issues with both methods:

      1. SQL Developer
      After using the Exporter for Microsoft Export, I had a XML file and a SQL file. There is a conversion process in SQL Developer in which I followed, however, it is not working.
      First I created a connection to the Oracle Database. I have created a repository. I have System Objects checked in Microsoft Access. I have set the user and group permissions in Access. I created a database for the Access file.
      The next step was to capture the previously created XML file. I did this. It appeared in the Captured Models view. From this point I am to right click on the captured model and select the Convert to Oracle Model option. I am then prompted to Set Data Map. After I select the apply button, the mode is suppose to be converted to Oracle. However, it does nothing at all. It doesnt give an error or tell me anything is wrong. I just does nothing. I am not sure what to do from this point.

      1. Apex directly
      After using the Exporter for Microsoft Export, I had a XML file and a SQL file.
      In Apex, there are two options for migrating. You can choose one of the 2 types. Access and Forms.
      I chose to try this process using Forms. I followed the steps up to uploading the XML file. When I selected finish. The following error was displayed:
      ORA-31011: XML parsing failed ORA-19202: Error occurred in XML processing LPX-00210: expected '<' instead of "¿" Error at line 1

      Line 1 of the XML file is the following: <?xml version="1.0" encoding="UTF-8" ?>

      I am very new to this process so I am not sure what they see as the error.

      I also tried to use the Access method. I uploaded the SQL file as instructed. Upon finishing, all of the tables migrated but all are Invalid. I am not really sure what that means or how to fix this issue.

      Please assist with one or both of these methods so that I can get a better understanding of the process and figure out why it is not working correctly. Thank you for your help.
        • 1. Re: MS Access to APEX Migration Errors/Problems NEED ASSISTANCE
          Hilary Farrell-Oracle
          Hi 971496,
          There are 2 ways in which this can be done.
          What you've outlined is not actually 2 ways to do the migration, they are 2 required steps in the migration process. Firstly, the database objects need to be migrated to Oracle using the migration tool in SQL Developer. Secondly, the application components are migrated using the migration utility in APEX - this step is dependent on the migrated database object existing in the schema associated with your APEX workspace. I would recommend reviewing Chapter 3, Migration a Microsoft Access Application, in the Application Express Migration Guide for an overview of the process: http://docs.oracle.com/cd/E37097_01/doc/doc.42/e35126/appmgr.htm#BCGBBDAC. Something to keep in mind is that a number of enhancements have been made in recent releases of SQL Developer, for MS Access migrations, so the options outlined in the documentation may differ to those in the new release.

          Now to address the issues you've outlined.

          SQL Developer*.
          Could you please provide version information for the following: SQL Developer, the version of MS Access are you attempting to migrate, and the Exporter version used?

          Based on the options you have mentioned, it sounds like you're using an older version of SQL Developer, because those options don't exist in the latest release. I would highly recommend downloading the latest release, SQL Developer 3.2.2, from OTN: http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html. With SQL Developer 3.2, you'll find two methods for carrying out an MS Access database migrations:

          1) "Copy to Oracle"
          This option was introduced with 3.0, I believe, and is a very quick and easy method of migrating your MS Access tables to Oracle. With this approach, you do not need to create or use a migration repository, or to capture and convert objects. This approach does not perform a complete migration. It only lets you copy the table, and optionally the table data, from the MS Access database to an Oracle database. It does not migrate or re-create primary and foreign key definitions and most constraints. (Any UNIQUE constraints or default values are not preserved in the copy. NOT NULL constraints are preserved in most cases, but not for Microsoft Access tables.) The approach also does not consider any non-table objects, such as procedures. From experience, the majority of customers wishing to migrate from MS Access to Oracle are primarily focused on the tables and data, and getting it moved across to Oracle quickly and easily. If this is your primary focus, and you're not too concerned about translating the queries, then this option could be for you. To use this option just create a connection to your MDB file, then right-click on the connection and select the "Copy to Oracle..." context menu item. Then you simply select the destination Oracle connection (NOTE: this should be the schema associated with your APEX workspace), specify whether to include data in the migration, and click Apply. I would recommend re-creating primary and foreign key definitions before proceeding to the application migration step.

          2) Migration Wizard - accessed via Tools > Migration > Migrate menu item
          This option is very close to the one you've attempted to use, and supports both an online and offline migration. The offline method makes use of the XML file generated by the Exporter tool, while the online method requires a connection to the MDB file itself in order to proceed. You might find the following Oracle By Example (OBE) helpful, as it outlines a step-by-step guide to using the Migration Wizard: http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/sqldev/r30/MSAccessMigration/MSAccessMigration.htm.

          For further information on database migration issues, you might also find it useful to refer to the SQL Developer forum: SQL Developer If you still experience issues migrating the database objects to Oracle, while using either of the above options, then please update this thread with the issues you hit and I can discuss them with the SQL Dev team.

          What version are you using?

          You mentioned that "+In Apex, there are two options for migrating. You can choose one of the 2 types. Access and Forms.+". Just to clarify things, the "Forms" option is specific to Oracle Forms migrations, while the "Access" option is for MS Access migrations. The files generated by the Exporter tool are specific to MS Access migrations, where the XML file is required by the offline migration wizard in SQL Developer, and the SQL script is used by the Migration Project wizard in APEX - so you must select the "Access" option when stepping through the wizard here. Before you proceed to this step of the migration, you must have completed the database migration step.

          Upon finishing, all of the tables migrated but all are Invalid. I am not really sure what that means or how to fix this issue.
          The items in your migration project appear invalid because the migrated database objects associated with the application components cannot be found in the schema associated with your APEX workspace. To fix this, you need to firstly complete the database migration and ensure that the migrated database objects are contained in the schema associated with your APEX workspace. Then recreate the migration project in APEX, and hopefully you should then see the objects listed as Valid. Something to keep in mind is that depending on the complexity of the original Forms and Reports, you may just wish to start from scratch with a new application and use the Create Application Wizard in APEX to generate a new application from scratch based upon the migrated database objects.

          If you require any additional information, just update this thread. I hope this helps.

          • 2. Re: MS Access to APEX Migration Errors/Problems NEED ASSISTANCE

            during migraton in Apex I choosed "Access", selected the sql File and during the migration Apex complained about:
            "ORA-06550: Zeile 8, Spalte 8: PLS-00172: Zeichenfolge zu lang"

            <br> p varchar2(32767) := null;
            <br> l_clob clob;
            <br> dbms_lob.createtemporary( l_clob, FALSE, dbms_lob.session );
            <br> dbms_lob.open( l_clob, dbms_lob.lob_readwrite );
            <br> p := 'Option Compare Database
            <br>Private Sub Form_Activate()
            ...and so on.

            If I push the Button "Back to Application" I get endless Errors ORA-02291.

            To me it seems that Apex can not process the Exporter generated sql File. Variable p seems to be too long (althogh its very long defined)

            I really appreciate your advice!

            thank you in advance