Reviewing Retrieved Queries

Next, review the queries retrieved from the Microsoft Access export. Application Migration identifies invalid queries as well as those without user interface defaults, which you can set before migration.

After you update the views, select the ones you want to include in the migration. If you do not include a query, any forms or reports based on the query are excluded from the migration.

To review retrieved queries:

1. From the project page, click Queries.

   The Queries page appears, showing the status of the objects ready for migration.

   
   Description of the illustration acmg_qry_pg.gif
   
   

   For each Microsoft Access query, the Queries page shows:

   • Oracle View - The corresponding Oracle view, which defaults to the Microsoft Access query name in all capital letters.

     If the Microsoft Access object was not successfully migrated to Oracle, then this field will not have a corresponding Oracle view name. Instead, it will contain a link to a page where you can create a corresponding Oracle view.

   • Status - Query status as either Valid or Invalid.

     Only valid queries can be included in the migration.

   • UI Defaults - Indicates if user interface defaults are set for the query.

     User interface defaults are used by Oracle Application Express to populate initial values for region and item properties. Using user interface defaults provides consistency across multiple applications or across multiple pages in an application.

2. To run a bulk process that attempts to compile all invalid queries, click Attempt to compile invalid queries.

   Using this option can validate some queries that show a status of invalid when initially migrated.

3. To create a view:

   1. On the Queries page, click the link in the Oracle View column for the view you want to create.

      The Oracle View appears. The editor is populated with the syntax of the original Microsoft Access query. You may need to edit the syntax to ensure it is valid Oracle syntax before compiling.

   2. Click the Compile button.

   3. From the list of object types, select View.

   4. Follow the on-screen instructions.

4. To edit a query:

   1. On the Queries page, click the Oracle view you want to edit.

   2. Click Compile to find the invalid part of the query.

      The Microsoft Access Query syntax appears in the edit window. It may require some modification to make it valid Oracle syntax.

   3. Click Access Query to review the initial query and compare it to the converted query.

   4. Update the query and recompile it.

   5. When it is validated, click the Queries breadcrumb.

   6. To include this validated query, select it in the left column on the Queries page and click Apply Changes.

5. To set user interface defaults:

   1. On the Queries page, click the Oracle view.

   2. In the Tasks list on the right, click UI Defaults.

   3. On the UI Defaults page, click Create Defaults.

   4. On the Create Table Dictionary Defaults page, click Create Defaults.

      The Table Dictionary page appears. Note that you are now working within SQL Workshop, the component of Oracle Application Express with utilities to load and unload data from an Oracle database, generate DDL, view object reports, and restore dropped database objects.

   5. To edit the information, click the Column name.

      The Column Defaults page appears. You can update the column label and change the sequence the columns will appear by default.

   6. To edit the information, click the Column name.

      The Column Defaults page appears. You can update the column label, change the sequence the columns will appear by default, and so on.

   7. Click Apply Changes to save your updates.

6. To include queries in the migration, select them in the left column.

7. Click Apply Changes to save your selections.