Oracle Application Express Migration Guide

Oracle® Application Express

Application Migration Guide

Release 4.2 for Oracle Database 12c

E17962-07

August 2013

Oracle Application Express Application Migration Guide, Release 4.2 for Oracle Database 12c

E17962-07

Primary Author: Terri Jennings

Contributors: Christina Cho, Joel Kallman, Sharon Kennedy, Hilary Farrell, David Peake

This software and related documentation are provided under a license agreement containing restrictions on

use and disclosure and are protected by intellectual property laws. Except as expressly permitted in your

license agreement or allowed by law, you may not use, copy, reproduce, translate, broadcast, modify, license,

transmit, distribute, exhibit, perform, publish, or display any part, in any form, or by any means. Reverse

engineering, disassembly, or decompilation of this software, unless required by law for interoperability, is

prohibited.

The information contained herein is subject to change without notice and is not warranted to be error-free. If

you find any errors, please report them to us in writing.

If this is software or related documentation that is delivered to the U.S. Government or anyone licensing it

on behalf of the U.S. Government, the following notice is applicable:

U.S. GOVERNMENT END USERS: Oracle programs, including any operating system, integrated software,

any programs installed on the hardware, and/or documentation, delivered to U.S. Government end users

are "commercial computer software" pursuant to the applicable Federal Acquisition Regulation and

agency-specific supplemental regulations. As such, use, duplication, disclosure, modification, and

adaptation of the programs, including any operating system, integrated software, any programs installed on

the hardware, and/or documentation, shall be subject to license terms and license restrictions applicable to

the programs. No other rights are granted to the U.S. Government.

This software or hardware is developed for general use in a variety of information management

applications. It is not developed or intended for use in any inherently dangerous applications, including

applications that may create a risk of personal injury. If you use this software or hardware in dangerous

applications, then you shall be responsible to take all appropriate fail-safe, backup, redundancy, and other

measures to ensure its safe use. Oracle Corporation and its affiliates disclaim any liability for any damages

caused by use of this software or hardware in dangerous applications.

Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of

their respective owners.

Intel and Intel Xeon are trademarks or registered trademarks of Intel Corporation. All SPARC trademarks

are used under license and are trademarks or registered trademarks of SPARC International, Inc. AMD,

Opteron, the AMD logo, and the AMD Opteron logo are trademarks or registered trademarks of Advanced

Micro Devices. UNIX is a registered trademark of The Open Group.

This software or hardware and documentation may provide access to or information on content, products,

and services from third parties. Oracle Corporation and its affiliates are not responsible for and expressly

disclaim all warranties of any kind with respect to third-party content, products, and services. Oracle

Corporation and its affiliates will not be responsible for any loss, costs, or damages incurred due to your

access to or use of third-party content, products, or services.

iii

Contents

Preface ................................................................................................................................................................ vii

Topic Overview .......................................................................................................................................... vii

Audience..................................................................................................................................................... viii

Documentation Accessibility................................................................................................................... viii

Related Documents ................................................................................................................................... viii

Conventions ................................................................................................................................................. ix

Changes in This Release ......................................................................................................................... xi

Changes in Oracle Application Express Release 4.2 .............................................................................. xi

1 Getting Started with Application Migration Workshop

About Migrating a Microsoft Access Application ............................................................................. 1-1

Preparation Checklist for Migrating Microsoft Access Applications......................................... 1-1

Analyze Your MDB File in Microsoft Access................................................................................. 1-2

About the Microsoft Access Migration to Oracle Application Express Forum ........................ 1-3

About Converting an Oracle Forms Application ............................................................................... 1-3

Preparation Checklist for Converting Oracle Forms Applications............................................. 1-3

About the Oracle Application Express Forum............................................................................... 1-4

2 Overview of the Migration Process

Why Migrate to Oracle Application Express?..................................................................................... 2-1

Before Starting the Conversion Process............................................................................................... 2-2

Evaluating Whether to Convert or Build an Application from Scratch......................................... 2-2

Understanding What an Oracle Application Express Application Conversion is Not .............. 2-2

Key Differences with Oracle Application Express ............................................................................ 2-3

Stateless Web Applications............................................................................................................... 2-3

Evaluating User Interface Design .................................................................................................... 2-3

About Business Logic Replication ................................................................................................... 2-4

Creating an Oracle Application Express Migration Project............................................................. 2-4

3 Migrating a Microsoft Access Application

How to Migrate a Microsoft Access Application ............................................................................... 3-1

Export Microsoft Access Metadata........................................................................................................ 3-2

Migrate the Microsoft Access Database to Oracle ............................................................................. 3-3

Verifying If Generated DDL Executes Against the Appropriate Instance ................................ 3-4

Create a Workspace and Add Oracle Application Express Users ................................................... 3-4

Creating a Workspace Manually ..................................................................................................... 3-5

Creating Oracle Application Express Users................................................................................... 3-7

Logging In To Your Oracle Application Express Workspace...................................................... 3-8

Create a Migration Project ...................................................................................................................... 3-9

About the Application Migrations Page...................................................................................... 3-10

Utilizing Interactive Reports .................................................................................................. 3-11

Review Your Retrieved Objects.......................................................................................................... 3-12

Reviewing Retrieved Tables .......................................................................................................... 3-12

Reviewing Retrieved Queries........................................................................................................ 3-16

Reviewing Retrieved Forms .......................................................................................................... 3-18

Reviewing Retrieved Reports........................................................................................................ 3-20

Reviewing Database, Module, and Pages Information ............................................................. 3-22

Generate the Oracle Application Express Application.................................................................. 3-22

Deleting a Migration Project............................................................................................................... 3-24

4 Converting an Oracle Forms Application

How to Convert Your Application ........................................................................................................ 4-1

Convert Oracle Forms to XML ............................................................................................................... 4-2

Converting FormModules, ObjectLibraries, or MenuModules to XML .................................... 4-2

About the Forms2XML Conversion Tool ................................................................................ 4-3

Using the Forms2XML Conversion Tool From a Command Line....................................... 4-3

Using the Forms2XML Conversion Tool in a Java Program ................................................ 4-4

Converting a PL/SQL Library ......................................................................................................... 4-4

Converting an Oracle Report to XML ............................................................................................. 4-5

Create a Workspace and Add Oracle Application Express Users ................................................... 4-5

Creating a Workspace Manually ..................................................................................................... 4-5

Creating Oracle Application Express Users................................................................................... 4-7

Logging In To Your Oracle Application Express Workspace...................................................... 4-8

Upload Database Objects into the Schema Associated with Your Workspace ............................ 4-9

Create a Conversion Project ................................................................................................................ 4-10

Review and Edit Forms Metadata ...................................................................................................... 4-11

About the Application Migrations Page...................................................................................... 4-11

Understanding the Project Page ............................................................................................ 4-12

Uploading Additional Files.................................................................................................... 4-14

Editing Project Details .................................................................................................................... 4-15

Reviewing Forms Modules............................................................................................................ 4-17

Viewing an Imported FormModule ..................................................................................... 4-17

Viewing Object Metadata and Selecting Specific Objects .................................................. 4-17

Viewing and Selecting Blocks ................................................................................................ 4-18

Viewing and Selecting Items.................................................................................................. 4-19

Reviewing Oracle Reports ............................................................................................................. 4-20

Viewing Reports....................................................................................................................... 4-20

Selecting Reports to Include................................................................................................... 4-21

Reviewing PL/SQL Libraries........................................................................................................ 4-21

About Annotations .................................................................................................................. 4-21

About Audit.............................................................................................................................. 4-21

Reviewing Forms Menus ............................................................................................................... 4-22

Viewing Form Menus.............................................................................................................. 4-22

Viewing a Summary of Forms Menus .................................................................................. 4-22

Viewing Form Menu and Program Unit Details................................................................. 4-23

Reviewing Object Libraries............................................................................................................ 4-24

About Annotations .................................................................................................................. 4-24

About Audit.............................................................................................................................. 4-24

Viewing an Uploaded Object Library................................................................................... 4-24

Using Annotations to Track the Conversion Process ................................................................ 4-25

Why Use Annotations? ........................................................................................................... 4-25

Generate the Oracle Application Express Application.................................................................. 4-26

Setting Up Application Defaults................................................................................................... 4-26

Creating an Application................................................................................................................. 4-26

Deleting a Migration Project............................................................................................................... 4-28

A Oracle Forms Generation Capabilities and Workarounds

Oracle Forms Modules ........................................................................................................................... A-1

Overview of Oracle Forms Components ....................................................................................... A-1

Alerts............................................................................................................................................ A-1

Blocks........................................................................................................................................... A-2

Canvases...................................................................................................................................... A-2

Coordinates................................................................................................................................. A-2

Editors.......................................................................................................................................... A-2

List of Values .............................................................................................................................. A-2

Program Units ............................................................................................................................ A-2

Property Classes......................................................................................................................... A-2

Record Groups............................................................................................................................ A-3

Triggers........................................................................................................................................ A-3

Visual Attributes ........................................................................................................................ A-3

Windows ..................................................................................................................................... A-3

About Generated Applications ....................................................................................................... A-3

Primary Key Assumptions ....................................................................................................... A-3

Block to Page Region Mappings ..................................................................................................... A-4

Single Record Block ................................................................................................................... A-4

Tabular Form .............................................................................................................................. A-5

Master Detail Blocks.................................................................................................................. A-5

Non-Database Block .................................................................................................................. A-6

List of Values Implementation ........................................................................................................ A-6

Implementing Business Logic ......................................................................................................... A-6

Processes...................................................................................................................................... A-7

Computations ............................................................................................................................. A-7

Validations .................................................................................................................................. A-7

Oracle Reports.......................................................................................................................................... A-8

PL/SQL Libraries ..................................................................................................................................... A-8

Forms Menus............................................................................................................................................ A-8

Object Libraries ....................................................................................................................................... A-8

Index

vii

Preface

Oracle Application Express Application Migration Guide describes how to convert

applications from either Microsoft Access or Oracle Forms to Oracle Application

Express.

To learn more about developing Oracle Application Express applications, see Oracle

Database 2 Day + Application Express Developer's Guide and "Quick Start" in Oracle

Application Express Application Builder User's Guide.

Topics:

■ Topic Overview

■ Audience

■ Documentation Accessibility

■ Related Documents

■ Conventions

Topic Overview

This document contains the following chapters:

Title Description

Changes in This Release Describes changes in this document for Oracle

Application Express, release 4.2.

Getting Started with Application

Migration Workshop

Provides an overview of the steps needed to migrate a

Microsoft Access application or Oracle Forms application

and generate an Oracle Application Express application.

Overview of the Migration Process Explains how specific types of logic is handled in Oracle

Application Express.

Migrating a Microsoft Access

Application

Explains how to migrate a Microsoft Access and generate

an Oracle Application Express application.

Converting an Oracle Forms

Application

Explains how to convert an Oracle Forms application and

generate an Oracle Application Express application.

Oracle Forms Generation

Capabilities and Workarounds

Describe how objects are generated during the conversion

process, or alternatively how the same functionality can

be implemented post-generation if necessary.

viii

Audience

Oracle Application Express Migration Guide is intended for application developers who

are building database-centric web applications using Oracle Application Express. This

guide describes how to migrate a Microsoft Access application or Oracle Forms

application and generate an Oracle Application Express application.

To use this guide, you must have a general understanding of relational database

concepts and an understanding of the operating system environment under which you

are running Oracle Application Express.

Documentation Accessibility

For information about Oracle's commitment to accessibility, visit the Oracle

Accessibility Program website at

http://www.oracle.com/pls/topic/lookup?ctx=acc&id=docacc.

Access to Oracle Support

Oracle customers have access to electronic support through My Oracle Support. For

information, visit

http://www.oracle.com/pls/topic/lookup?ctx=acc&id=info or visit

http://www.oracle.com/pls/topic/lookup?ctx=acc&id=trs if you are

hearing impaired.

Related Documents

For more information, see these Oracle resources:

■ Oracle Application Express Application Builder User's Guide

■ Oracle Application Express Release Notes

■ Oracle Application Express Installation Guide

■ Oracle Database 2 Day + Application Express Developer's Guide

■ Oracle Application Express End User's Guide

■ Oracle Application Express Administration Guide

■ Oracle Application Express SQL Workshop Guide

■ Oracle Application Express API Reference

■ Oracle Database Concepts

■ Oracle Database Advanced Application Developer's Guide

■ Oracle Database Administrator's Guide

■ Oracle Database SQL Language Reference

■ SQL*Plus User's Guide and Reference

For additional documentation available on Oracle Technology Network (OTN), visit

the Oracle Application Express website located at:

http://www.oracle.com/technetwork/developer-tools/apex/overview/index.h

tml

See Also: Oracle Database 2 Day + Application Express Developer's

Guide

For additional application examples, go to the Learning Library. Search for free online

training content, including Oracle by Example (OBE), demos, and tutorials. To access

the Oracle Learning Library, go to:

http://www.oracle.com/technetwork/tutorials/index.html

Printed documentation is available for sale in the Oracle Store at:

http://shop.oracle.com/

If you have a user name and password for OTN, then you can go directly to the

documentation section of the OTN website at:

http://www.oracle.com/technetwork/indexes/documentation/index.html

Conventions

The following text conventions are used in this document:

Convention Meaning

boldface Boldface type indicates graphical user interface elements associated

with an action, or terms defined in text or the glossary.

italic Italic type indicates book titles, emphasis, or placeholder variables for

which you supply particular values.

monospace Monospace type indicates commands within a paragraph, URLs, code

in examples, text that appears on the screen, or text that you enter.

Changes in This Release

This preface contains:

■ Changes in Oracle Application Express Release 4.2

Changes in Oracle Application Express Release 4.2

The following are changes in Oracle Application Express Application Migration Guide for

Oracle Application Express release 4.2.

■ All content has been updated to reflect new functionality.

■ Screen captures and graphics have been added and updated to reflect Oracle

Application Express release 4.2 user interface enhancements.

xii

Getting Started with Application Migration Workshop 1-1

1Getting Started with Application Migration

Workshop

Oracle Application Express Application Migration Workshop (Application Migrations)

enables you to migrate a Microsoft Access application or convert an Oracle Forms

application to an Oracle Application Express application.

This section provides a general description of the steps needed to migrate applications

from Microsoft Access or Oracle Forms to Oracle Application Express to Oracle

Application Express.

Topics:

■ About Migrating a Microsoft Access Application

■ About Converting an Oracle Forms Application

About Migrating a Microsoft Access Application

You begin the migration process by exporting your Microsoft Access metadata using

the Exporter tool and Oracle SQL Developer. After this initial step, you use

Application Migration to review the retrieved objects and resolve any issues regarding

invalid objects. As the final step, you have the option of generating either an

application based on valid forms and reports or a maintenance application based on

valid tables and views.

Once you have generated the application, you can take advantage of all the

functionality in Oracle Application Express to further develop and enhance the

migrated application.

Topics:

■ Preparation Checklist for Migrating Microsoft Access Applications

■ Analyze Your MDB File in Microsoft Access

■ About the Microsoft Access Migration to Oracle Application Express Forum

Preparation Checklist for Migrating Microsoft Access Applications

Before you begin the migration process, verify that your system meets these

requirements:

■ Oracle Application Express version 3.0 or later

You must install Oracle Application Express 3.0 or later. You use the Application

Migration feature within Oracle Application Express to download the Exporter

About Migrating a Microsoft Access Application

1-2 Oracle Application Express Application Migration Guide

tool and to migrate Microsoft Access forms and reports to Oracle Application

Express.

■ Oracle SQL Developer 2.1 or later (available in English only)

You must install Oracle SQL Developer version 2.1 or later. You use the migration

capabilities extension to migrate the Microsoft Access schema and data to Oracle.

■ Exporter tool version 10.2.0.2.5 or later (available in English only)

You can install the Exporter tool using the Download Exporter link within Oracle

Application Express. For instructions, see "Export Microsoft Access Metadata" on

page 3-2.

If you are using Oracle SQL Developer version 2.1 or later, you can access the

Exporter tool directly from the Migration menu.

■ Microsoft Access

Your local system must have Microsoft Access installed, and it must be installed

where the Exporter tool and .mdb file reside.

■ Microsoft Data Access Components (MDAC)

Your local system should have the latest version of Microsoft Data Access

Components (MDAC) installed. You can download the latest version from the

Microsoft website.

■ Printer

Your local system must have a printer installed so that a report can be opened in

design view. This is a requirement for exporting your Reports information from an

.mdb file.

■ Analyze your .mdb file before you export your database. Follow the instructions

in the next section.

Analyze Your MDB File in Microsoft Access

Using Microsoft Access, you should analyze your .mdb file before you export your

database. Performing these steps minimizes errors in the migration.

1. In Microsoft Access, analyze the objects in your .mdb file:

a. From the Microsoft Access menu bar, select To ol s, Analyze, and then

Documenter.

b. Select the All Object Types tab, and then click the Select All button to select

all the objects within the application for analysis.

c. Remove or resolve any erroneous objects reported by the Documenter.

2. Ensure the application contains no missing references:

a. In Microsoft Access, launch the design IDE (press Alt+F11 keys).

b. From the menu bar, select Tools and then References.

c. Remove or resolve any missing references.

3. Ensure the application compiles successfully:

a. From the menu bar in the IDE view, select Debug and then Compile.

b. Resolve any reported errors.

4. Perform a compact and repair on the database:

About Converting an Oracle Forms Application

Getting Started with Application Migration Workshop 1-3

■ From the menu bar in Microsoft Access, select Tools, Database Utilities, and

then Compact and Repair Database.

5. Ensure that all linked tables are valid links:

a. From the menu bar in Microsoft Access, select Tools, Database Utilities, and

then Linked Table Manager.

b. Verify that all links are up-to-date and pointing to an existing .mdb file that is

not read-only.

About the Microsoft Access Migration to Oracle Application Express Forum

In addition to Oracle support, you can post questions on the Database and

Applications Migrations forum:

http://forums.oracle.com/forums/category.jspa?categoryID=27

About Converting an Oracle Forms Application

You begin the process by converting Oracle Forms metadata to Extensible Markup

Language (XML). Then, you create a conversion project within Oracle Application

Express. Once you have reviewed and edited the application metadata within the

project, you generate an Oracle Application Express application. Then, you can

customize your application and change application attributes or add new pages within

Application Builder.

Topics:

■ Preparation Checklist for Converting Oracle Forms Applications

■ About the Oracle Application Express Forum

Preparation Checklist for Converting Oracle Forms Applications

Before you begin the conversion process, you must:

■ Install Oracle Application Express version 3.2 or later

You must install Oracle Application Express 3.2 or later to convert an Oracle

Forms application to Oracle Application Express.

■ Install Oracle9i Oracle Developer Suite or later

You must install Oracle Developer Suite to convert Oracle Forms applications and

Oracle Reports to XML format. Specifically, you need:

– Oracle Forms Builder

You must have Oracle Forms Builder installed to access the Forms2XML

conversion tool and the File Conversion utility for converting Oracle Forms

application files.

– Oracle Reports Builder

You must have Oracle Reports installed, to convert Oracle Reports (including,

binary (.RDF), ASCII (.REX), and .JSP) to XML format.

■ Convert Oracle Forms Metadata

To start the conversion process, you convert:

See Also: "Migrating a Microsoft Access Application" on page 3-1

About Converting an Oracle Forms Application

1-4 Oracle Application Express Application Migration Guide

– Oracle Forms FormModule (.FMB), ObjectLibrary (.OLB), or MenuModule

(.MMB) files to XML format using the Forms to XML conversion tool,

Forms2XML

– Oracle Reports (including, binary (.RDF), ASCII (.REX), and .JSP) to XML

format using the File Conversion option in Reports Builder

– PL/SQL library .PLL files to .PLD text files using the File Conversion option in

Oracle Forms Builder

■ Associate Oracle Forms Schema with Oracle Application Express workspace

You must ensure the schema associated with your Oracle Application Express

workspace contains the database objects for your Oracle Forms application.

Further information on Oracle Application Express workspaces and associating

schemas is provided in subsequent chapters.

About the Oracle Application Express Forum

In addition to Oracle support, you can post questions on the Oracle Application

Express Forum:

http://forums.oracle.com/forums/forum.jspa?forumID=137

See Also: "Converting an Oracle Forms Application" on page 4-1

Overview of the Migration Process 2-1

2Overview of the Migration Process

Whether converting from Oracle Forms or migrating from Microsoft Access, Oracle

Application Express Application Migration is an effective tool to start your conversion

project. This section outlines the tool’s capabilities and provides important information

that should be reviewed before starting a conversion project.

Topics:

■ Why Migrate to Oracle Application Express?

■ Before Starting the Conversion Process

■ Evaluating Whether to Convert or Build an Application from Scratch

■ Understanding What an Oracle Application Express Application Conversion is

Not

■ Key Differences with Oracle Application Express

■ Creating an Oracle Application Express Migration Project

Why Migrate to Oracle Application Express?

Oracle Forms modernization projects are often undertaken to convert legacy

applications to the latest Oracle Database version and enable developers to satisfy user

demands for greater user interactivity and Web 2.0 capabilities.

Microsoft Access presents organizations with a myriad of manageability issues. Often

simple applications grow in size and complexity and become mission critical. These

same applications are often scattered throughout the network making maintenance,

backups, security, and access increasingly costly and inconsistent. One key reason for

migrating from Microsoft Access to an Oracle Database is to consolidate the data and

build common applications based on a single data source.

Migrating to Oracle Application Express provides a robust, scalable, secure application

development tool that takes full advantage of the Oracle Database. Oracle Application

Express requires only a web browser and no client software for development,

deployment, or run time. The resulting web pages are rendered as hypertext mark-up

language (HTML). The Oracle Application Express development environment

provides many out-of-the-box features, such as interactive reports and charts, and

enables developers to rapidly deploy web application that greatly enhance user

interactivity.

Before Starting the Conversion Process

2-2 Oracle Application Express Application Migration Guide

Before Starting the Conversion Process

Before starting a conversion project, it is important to learn how to build applications

with Oracle Application Express. The first step is to install Oracle Application Express

within an Oracle Database instance. Oracle Application Express requires an Oracle

database that is release 10.2.0.4 or later. To learn more, see Oracle Application Express

Installation Guide. To learn how to build applications based on existing Oracle database

tables, review Oracle Database 2 Day + Application Express Developer's Guide.

The Oracle Application Express development environment utilizes wizards within a

declarative environment and enables you to easily extend components using SQL and

PL/SQL. You can further extend the Oracle Application Express framework by

incorporating JavaScript and Asynchronous JavaScript and XML (AJAX). To learn

more, see "Implementing Business Logic" on page A-6.

Evaluating Whether to Convert or Build an Application from Scratch

Thanks to how quickly you can build a new application within Oracle Application

Express, it may seem quicker to build an application from scratch rather than loading

the source application definitions into an Oracle Application Express conversion

project and then generating an initial design.

When compared with developing an application from scratch, converting an existing

application provides many benefits. Once the source files for the original application

are loaded into the Oracle Application Express project, all developers can review the

pertinent information without needing to refer back to Oracle Forms or Microsoft

Access. There is no need for developers to become familiar with the source tool

development environment.

Having the forms, reports, and queries loaded into a conversion project also provides

an easy way to review the functionality and application logic provided in the old

application. Even if these components are not used to generate an Oracle Application

Express application, you can view, copy, and reuse the underlying SQL.

When converting from Oracle Forms another major benefit is the ability to track the

conversion to ensure you fully implement all the business logic within the new

application. By utilizing annotations within the conversion project, you can assign

priorities, resources, and add notes or tags to any component. Completion progress

can also be tracked to provide low-level project management capabilities.

Understanding What an Oracle Application Express Application

Conversion is Not

When you convert an application to Oracle Application Express, you do not simply

load the source files, press a few buttons, and magically generate a completed

application. The conversion capabilities are designed to provide an initial design that

you then must enhance and expand to fully replicate the original application. The

generated applications use the same Create Application wizard that you use to build

applications based on existing Oracle tables or views. However, instead of needing to

specify the generated pages, these are determined from the information loaded into

the conversion project. For more information on how various source components are

generated, see "Oracle Forms Generation Capabilities and Workarounds" on page A-1.

Once you have generated the initial design, you can take full advantage of the rapid

application development capabilities available in Oracle Application Express to

enhance the generated pages. Built-in wizards also enable you to quickly expand your

new application by adding new pages and links as necessary.

Key Differences with Oracle Application Express

Overview of the Migration Process 2-3

Key Differences with Oracle Application Express

Understanding some of the key differences between Oracle Application Express and

Oracle Forms or Microsoft Access will greatly assist you in redeveloping your

applications.

Topics:

■ Stateless Web Applications

■ Evaluating User Interface Design

■ About Business Logic Replication

Stateless Web Applications

Oracle Application Express does not maintain a constant state between the client (that

is, the web browser) and the database, but performs stateless transactions. A database

session is only utilized when requesting or submitting a page. Because Oracle

Application Express is stateless between the start and the end of a logical transaction,

(for example, when updating a record,) it is imperative to use optimistic locking rather

than pessimistic locking.

Oracle Forms and Microsoft Access use pessimistic locking whereby the record is

locked when a user requests a record for update. This lock is maintained until the

record is completed (or committed) or canceled (or rolled back). One of the key reasons

for not using pessimistic locking within a stateless web application is that if a user

closes their web browser or looses their connection in the middle of a transaction, the

record would remain locked.

Once form pages are generated by the Create Application or Create Page wizards,

Automatic Row Fetch and Automatic Row Processing processes are generated which

incorporate the MD5 checksum validation to enforce the optimistic locking. Before the

record is sent to the database to be updated or deleted the MD5 initially created is

compared to the MD5 value of the current database record to ensure they are the same.

If they differ, then the database record has been updated since it was queried and an

error displays to the user.

One consequence of an HTML application is that a rendered page does not perform

actions such as validations or hiding and showing fields as the user interacts with the

page. Validations are performed when the user submits the page for processing.

However, you can change this default behavior by utilizing JavaScript and AJAX. The

time needed to load a page is influenced by the transmission speed and the speed with

which the web browser renders the page. Therefore, it is not advisable to attempt to

completely replicate the client-side interactivity within an HTML application as the

weight (or size) of the page adversely affects page performance. As a best practice,

only implement critical client-side actions.

Evaluating User Interface Design

Oracle Forms and Microsoft Access use different underlying technologies to present

screens to users. In contrast, an Oracle HTTP Server renders HTML through a web

browser. As a consequence, the design of screens and items is markedly different.

When thinking about screen design, some screen layouts commonly used in Oracle

Forms are not readily reproducible in Oracle Application Express. Oracle Application

Express renders a complete HTML page and does not support the concept of windows

and canvases. Oracle Application Express also has a limitation of one tabular form per

page. Therefore, it is important to understand that it may be difficult to exactly

Creating an Oracle Application Express Migration Project

2-4 Oracle Application Express Application Migration Guide

reproduce the screen layouts of the source application. This difference in screen design

presents opportunities to redesign some of the key business processes and potentially

streamline them, or include additional paths to provide greater flexibility.

For example, a common layout in Oracle Forms, such as a master detail form, can be

redeployed using a master form with reports for each of the detail views that link to

separate pages for editing these details. These same detail pages can also be accessed

from elsewhere in the application very easily.

Oracle Forms often uses the same screen for both querying records and then updating

a single record at a time. This is achieved by performing a query directly on the form

used for updating a single record. Oracle Application Express does not support the

notion of Enter Query and Execute Query. Therefore, the conversion project generates

an interactive report where records can be queried with a link to a form for editing a

single record.

Oracle Application Express does not support coordinate positioning of fields on a page

out-of-the-box, but uses a grid layout to set out elements such as labels and items.

Items can be specified with attributes (such as New Line, New Field, Column Span,

and Row Span) to determine how items are located.

About Business Logic Replication

One of the major facets of converting from either Oracle Forms or Microsoft Access is

replicating the business user interface specific logic. It is important to separate

business logic from logic for manipulating the user interface. Given the differences in

user interface implementation, the majority of the associated logic from the original

application will no longer be relevant. Oracle Application Express uses processes,

computations, and validations to implement business logic. Some logic can also be

replicated by utilizing default and source values within the Oracle Application

Express item definition.

The key to ensuring your new application performs correctly is to understand how

and when each of these functions operate. The ability to add conditional logic to any

component within Oracle Application Express (including regions, items, branches,

processes, computations and validations) also provides extensive flexibility when

replicating the original business logic. For more information, see "Implementing

Business Logic" on page A-6.

Creating an Oracle Application Express Migration Project

In an Oracle Forms conversion project, the data structures are defined in an Oracle

Database and do not necessarily need to be migrated. However, with Microsoft Access

the data structures and data must first be migrated across to Oracle using SQL

Developer. For more information on how to migrate data structures "Migrating a

Microsoft Access Application" on page 3-1.

Once the data structures are correctly implemented into a schema accessible to Oracle

Application Express, then the basic process for conducting a conversion is to load the

source application files into a project. The project is designed to show all the applicable

components and provide the ability to generate an initial application design. After

reviewing the project components and selecting the valid screens, reports, and items to

be included, you can generate an Oracle Application Express application. With both a

Forms Conversion project and an Access Migration project, some components loaded

into a project cannot be generated. To learn more about generation capabilities and

workarounds, see "Oracle Forms Generation Capabilities and Workarounds" on

page A-1.

Migrating a Microsoft Access Application 3-1

Migrating a Microsoft Access Application

This section describes the steps to migrate applications from Microsoft Access to

Oracle Application Express.

Topics:

■ How to Migrate a Microsoft Access Application

■ Export Microsoft Access Metadata

■ Migrate the Microsoft Access Database to Oracle

■ Create a Workspace and Add Oracle Application Express Users

■ Create a Migration Project

■ Review Your Retrieved Objects

■ Generate the Oracle Application Express Application

■ Deleting a Migration Project

How to Migrate a Microsoft Access Application

Before You Begin

Read "Preparation Checklist for Migrating Microsoft Access Applications" on page 1-1.

To migrate applications from Microsoft Access to Oracle Application Express, you

must perform the steps described in this section. The following illustration outlines the

entire process:

Export Microsoft Access Metadata

3-2 Oracle Application Express Application Migration Guide

The migration process consists of the following steps:

Export Microsoft Access Metadata

Migrate the Microsoft Access Database to Oracle

Create a Workspace and Add Oracle Application Express Users

Create a Migration Project

Review Your Retrieved Objects

Generate the Oracle Application Express Application

Export Microsoft Access Metadata

To export your metadata from Microsoft Access, download the correct version of the

Exporter tool, as explained in this section. Then, run the Exporter and extract the

metadata from the Microsoft Access .mdb file. The metadata contains the necessary

database and application schema information.

The export process creates two output files:

■ database (.xml file)

■ application (.sql file)

Exporter Tool System Requirements

To use the Exporter tool, the Microsoft Windows operating system where the Exporter

tool runs must include the following libraries:

■ Microsoft DAO 3.6 Object Library

■ Microsoft ActiveX Data Objects 2.5 Library

Important: You must follow the steps in the exact sequence

presented in this section.

Migrate the Microsoft Access Database to Oracle

Migrating a Microsoft Access Application 3-3

Downloading the Exporter Tool

To download the Exporter tool for Microsoft Access:

1. Log in the workspace you created for your conversion project as described in

"Logging In To Your Oracle Application Express Workspace" on page 3-8.

2. Click the Application Builder icon.

3. Click Migrations on the right side of the page.

4. Under Tasks, click Download Exporter for Microsoft Access.

5. In the Download column, click the zip file that corresponds to your version of

Microsoft Access. For example, download the omwb2003.zip file if you are using

Microsoft Access 2003.

6. Save the file.

7. Unzip the file. You must replace the following files with the updated versions in the

msaccess_exporter directory where you unzipped the Exporter tool:

■ schema.dtd file

■ Exporter tool file: omwb<version>.mde

■ online help file: omwb.chm

Be sure to invoke the export from this directory.

Exporting Your Metadata

To export your metadata, follow the instructions found in the help file for the Exporter

tool. To find the instructions, do one of the following:

■ Launch the Exporter tool, and click the Help button.

■ Open the help file (omwb.chm) contained in the Exporter zip file.

The instructions appear in the topic called Exporter Overview.

Migrate the Microsoft Access Database to Oracle

To migrate the Microsoft Access database to Oracle:

1. Start Oracle SQL Developer version 2.1 or later. For example:

Note: Follow the instructions in this section if you are downloading

the Exporter tool from Oracle Application Express release 3.0 or later.

Alternately, you skip the following task and access the Exporter

directly from Oracle SQL Developer using the Migration menu.

See Also: "Migrate the Microsoft Access Database to Oracle" on

page 3-3 and "How to Migrate a Microsoft Access Application" on

page 3-1

Create a Workspace and Add Oracle Application Express Users

3-4 Oracle Application Express Application Migration Guide

Create an Oracle user named MIGRATIONS with the default tablespace USER

and temporary tablespace TEMP and grant it at least RESOURCE, CREATE

SESSION, and CREATE VIEW privileges.

b. Create a database connection named Migration_Repository that connects

to the MIGRATIONS user.

c. Right-click the connection, select Migration_Repository, and then select

Migration Repository and then Associate Migration Repository to create the

repository.

2. Load the database metadata (.xml file) created in step 1. On the Oracle SQL

Developer toolbar, select Tools, Migration, and then Capture Microsoft Access

Exported XML.

This captures the schema and creates a Captured Model of the Microsoft Access

database.

3. Convert the captured database schema to Oracle. Right-click Captured Model and

select Convert to Oracle Model.

4. Generate the migrated Oracle database schema. Right-click Converted Model and

select Generate.

The generated DDL statements should execute against your Oracle database

instance, to generate the migrated schema objects.

For more information about Oracle SQL Developer migration capabilities, see:

http://www.oracle.com/technetwork/database/migration/index.html

Verifying If Generated DDL Executes Against the Appropriate Instance

When using Oracle SQL Developer migration capabilities, you must verify that the

generated DDL statements are executed against the same instance where Oracle

Application Express 3.0 or later is installed. If you select a Least Privilege Schema

Migration, the migrated objects can be created in an existing schema on your database

instance. Otherwise, a schema of the same name as the captured database (for

example, Northwind) is created on your database instance.

The migrated Oracle database schema objects must be in the same instance where

Oracle Application Express 3.0 is installed. If they are not, you will not be able to

complete the next step.

Create a Workspace and Add Oracle Application Express Users

A workspace is a virtual private database allowing multiple users to work within the

same Oracle Application Express installation while keeping their objects, data and

applications private. Each workspace has a unique ID and name.

An Oracle Application Express administrator can create a workspace manually within

Oracle Application Express Administration Services or have users submit requests.

Oracle Application Express Administration Services is a separate application for

managing an entire Oracle Application Express instance. See "Creating Workspaces"

and "Managing Workspace Requests" in Oracle Application Express Administration

Guide.

See Also: "Create a Workspace and Add Oracle Application Express

Users" on page 3-4 and "How to Migrate a Microsoft Access

Application" on page 3-1

Create a Workspace and Add Oracle Application Express Users

Migrating a Microsoft Access Application 3-5

Topics:

■ Creating a Workspace Manually

■ Creating Oracle Application Express Users

■ Logging In To Your Oracle Application Express Workspace

Creating a Workspace Manually

To create an Oracle Application Express workspace manually:

1. Log in to Oracle Application Express Administration Services.

Oracle Application Express Administration Services is a separate application for

managing an entire Oracle Application Express instance. You log in using the

ADMIN account and password created or reset during the installation process.

a. In a web browser, navigate to the Oracle Application Express Administration

Services application. By default, Oracle Application Express Administration

Services installs to the following location:

– If your setup uses the Oracle Application Express Listener, go to:

http://hostname:port/apex/apex_admin

Where:

hostname is the name of the system where Oracle Application Express

Listener is installed.

port is the port number assigned to Oracle Application Express Listener.

In a default installation, this number is 8080. These defaults are correct at

the time of the writing of this document. To learn more, see Oracle Applica-

tion Listener Installation and Developer Guide

apex is the service name defined when configuring the Oracle Applica-

tion Express Listener.

– If your setup uses the embedded PL/SQL gateway, go to:

http://hostname:port/apex/apex_admin

Where:

hostname is the name of the system where Oracle XML DB Protocol

Server is installed.

port is the port number assigned to Oracle XML DB Protocol Server. In a

default installation, this number is 8080.

apex is the database access descriptor (DAD) defined in the configuration

file.

– If your setup uses Apache and mod_plsql, go to:

http://hostname:port/pls/apex/apex_admin

Where:

hostname is the name of the system where Oracle HTTP Server is

installed.

See Also: Oracle Database 2 Day + Application Express Developer's

Guide if you are new to Oracle Application Express

Create a Workspace and Add Oracle Application Express Users

3-6 Oracle Application Express Application Migration Guide

port is the port number assigned to Oracle HTTP Server. In a default

installation, this number is 7777.

pls is the indicator to use the mod_plsql cartridge.

apex is the database access descriptor (DAD) defined in the mod_plsql

configuration file.

b. On the Login page:

– In Username, enter admin.

– In Password, enter the Oracle Application Express administrator account

password you specified when you installed Oracle Application Express.

– Click Login.

Next, create a workspace.

2. Click Manage Workspaces.

3. Under Workspace Actions, click Create Workspace.

The Create Workspace Wizard appears.

4. For Identify Workspace, enter the following:

a. Workspace Name - Enter a unique workspace name.

b. Workspace ID - Leave Workspace ID blank to have the new Workspace ID

automatically generated. A Workspace ID must be a positive integer greater

than 100000.

c. Workspace Description - Enter a workspace description.

d. Click Next.

5. For Identify Schema, specify whether you are re-using an existing schema or

creating a new one.

If you are using an existing schema:

a. For Re-use existing schema, select Yes.

b. Select a schema from the list.

c. Click Next.

If you are creating a new schema:

a. For Re-use existing schema, select No.

b. Enter a schema name and password.

c. Specify a space quota.

d. Click Next.

6. For Identify Administrator, enter the Workspace administrator information and

click Next.

7. Confirm your selections and click Create Workspace.

See Also: See "Logging in to Oracle Application Express

Administration Services" in Oracle Application Express Administration

Guide.

Create a Workspace and Add Oracle Application Express Users

Migrating a Microsoft Access Application 3-7

Creating Oracle Application Express Users

To create an Oracle Application Express user account:

1. Log in to Oracle Application Express Administration Services. See "Logging in to

Oracle Application Express Administration Services" in Oracle Application Express

Administration Guide.

2. Click Manage Workspaces.

3. Under Manage Workspaces, click Manage Developers and Users.

The Manage Developers and Users page appears.

4. Click Create User.

The Create/Edit User page appears.

5. Under User Attributes, enter:

a. Username - Enter the username used to log in to the system. Restrictions

include:

– Maximum length of 100 characters

– No spaces

– Only these special characters are permitted: ampersand (@) and period (.)

b. Email Address - Enter the valid email address for this user.

c. First Name - Enter the first or given name to further identify the user

(optional).

d. Last Name - Enter the last or family name to further identify the user

(optional).

e. Description - Enter comments about this user (optional).

6. Under Account Privileges:

a. Workspace - Select a workspace in which to create the user.

b. Default Schema - Specify the default schema used for data browsing,

application creation, and SQL script execution.

c. User is an administrator - Specify if this user should have workspace

administrator privileges.

Administrators are given access to all components. Additionally, they can

manage user accounts, groups, and development services. Components may

not be available if they are switched off by Instance Administrators.

d. User is a developer - Specify if this user should have developer privileges.

Developers must have access to either Application Builder, SQL Workshop, or

both. Components may not be available if they are switched off by Instance

Administrators.

e. Application Builder Access - Determines whether a developer has access to

the Application Builder.

f. SQL Workshop Access - Determines whether a developer has access to the

SQL Workshop.

g. Team Development Access - Determines whether a developer has access to

the Team Development.

Create a Workspace and Add Oracle Application Express Users

3-8 Oracle Application Express Application Migration Guide

Account Availability - Select Locked to prevent the account from being used.

Select Unlocked to allow the account to be used.

7. Under Password:

■ Password - Enter a case-sensitive password.

■ Confirm Password - Enter the password again.

■ Require Change of Password On First Use - Select No to require this user to

change his or her password at first log in. Select Yes to require the user to

change the password immediately after logging in the first time.

8. Click Create.

Logging In To Your Oracle Application Express Workspace

Once you create a workspace, you must log in to it using your login credentials (that

is, the workspace name, user name, and password).

To log in to a workspace:

1. In a web browser, navigate to the Oracle Application Express Login page. By

default, Oracle Application Express installs to the following location:

■ If your setup uses the Oracle Application Express Listener, go to:

http://hostname:port/apex

Where:

hostname is the name of the system where Oracle Application Express

Listener is installed.

port is the port number assigned to Oracle Application Express Listener. In a

default installation, this number is 8080. These defaults are correct at the time

of the writing of this document. To learn more, see Oracle Application Listener

Installation and Developer Guide

apex is the service name defined when configuring the Oracle Application

Express Listener.

■ If your setup uses the embedded PL/SQL gateway, go to:

http://hostname:port/apex

Where:

hostname is the name of the system where Oracle XML DB Protocol Server is

installed.

port is the port number assigned to Oracle XML DB Protocol Server. In a

default installation, this number is 8080.

apex is the database access descriptor (DAD) defined in the configuration file.

■ If your setup uses Oracle HTTP Server (Apache) and mod_plsql, go to:

http://hostname:port/pls/apex

Where:

See Also: See "Creating Workspaces" and "Managing Workspace

Requests" in Oracle Application Express Administration Guide

Create a Migration Project

Migrating a Microsoft Access Application 3-9

hostname is the name of the system where Oracle HTTP Server is installed.

port is the port number assigned to Oracle HTTP Server. In a default

installation, this number is 7777.

pls is the indicator to use the mod_plsql cartridge.

apex is the database access descriptor (DAD) defined in the mod_plsql

configuration file.

The Login page appears.

2. Under Login, enter the following:

■ In the Workspace field, enter the name of your workspace.

■ In the Username field, enter your user name.

■ In the Password field, enter your case-sensitive password.

3. Click Login.

Depending on your setup, you might be required to change your password when

you log in for the first time.

Create a Migration Project

To create a migration project:

1. Log in the workspace you created for your conversion project as described in

"Logging In To Your Oracle Application Express Workspace" on page 3-8.

2. Click the Application Builder icon.

3. Click Migrations on the right side of the page.

4. On the Application Migrations page, click Create Project.

The Create Migration Project wizard appears. The steps included in the wizard

appear in a graphic at the top of the page.

5. Enter the project details:

a. Project Name - Enter a unique name. For example, consider using the same

name as the Microsoft Access .mdb file you used to create the project.

b. Type - Select Access.

c. Description - Enter a meaningful description for this project. For example,

describe the Microsoft Access .mdb file that you used to create the project.

d. Schema - Select the schema.

The default schema is the schema associated with your workspace. If multiple

schemas are associated with your workspace, all associated schemas appear in

the select list, arranged in alphabetical order. When this situation exists, select

the schema associated with the SQL script you want to upload.

e. Migration Export File - Locate the .sql file created by the Exporter tool for

Microsoft Access.

Create a Migration Project

3-10 Oracle Application Express Application Migration Guide

Click Next.

6. Review the project details, and click Finish.

The project page appears.

This page displays as an interactive report. To customize the report, use the Search

bar at the top of the page. See "Customizing Interactive Reports" in Oracle

Application Express Application Builder User's Guide.

About the Application Migrations Page

The Application Migrations page initially shows a high-level overview of the

Microsoft Access objects retrieved from your Microsoft Access database.

This page displays the project name, project type, related application, and number of

objects.

After you generate an application from a migration project, the application name

displays in the Application column. To link to the application, select the application

name.

To view project details, click the project name. The project page appears.

See Also: "About the Application Migrations Page" on page 3-10,

"Review Your Retrieved Objects" on page 3-12, and "How to Migrate a

Microsoft Access Application" on page 3-1

Create a Migration Project

Migrating a Microsoft Access Application 3-11

Note that your project might not include all object types. The project page lists only

object types that exist in your database.

Utilizing Interactive Reports

Many pages in Application Migration Workshop display as interactive reports. You

can customize the appearance of interactive reports using the Search bar at the top of

each page.

Use the Search bar to create custom searches and customize and filter the information

that appears on the page.

Available controls on the Search bar include:

See Also: "Review Your Retrieved Objects" on page 3-12 and "How

to Migrate a Microsoft Access Application" on page 3-1

Review Your Retrieved Objects

3-12 Oracle Application Express Application Migration Guide

■ Select columns to search icon - Resembles a magnifying glass. Click this icon to

narrow your search to specific columns. To search all columns, select All

Columns.

■ Text area - Enter case insensitive search criteria (wildcard characters are implied)

and then click Go.

■ Go button - Executes a search.

■ Actions menu - Use the Actions menu to customize an interactive report.

To learn more, see "Customizing Interactive Reports" in Oracle Application Express

Application Builder User's Guide.

Review Your Retrieved Objects

Next, you want to select the objects to include in the migration. The initial list consists

of the Microsoft Access application metadata that is retrieved, both valid and invalid.

To include an object, it must have a status of Valid. By default, all objects with a Valid

status are selected.

From within Application Migration, you can fix objects identified as invalid so that

they can be included. Since the Application Migration also identifies tables without

primary keys and objects without user interface defaults, you can correct those

situations to maximize application design recovery.

Topics:

■ Reviewing Retrieved Tables

■ Reviewing Retrieved Queries

■ Reviewing Retrieved Forms

■ Reviewing Retrieved Reports

■ Reviewing Database, Module, and Pages Information

Reviewing Retrieved Tables

Next, review the Oracle tables retrieved from the Microsoft Access database.

Application Migration identifies invalid tables without primary keys and those

without user interface defaults, which you can add before migrating.

After you update the tables, select the ones you want to include in the migration. If

you do not include a table, all forms and reports based on the table are excluded from

the migration.

To review retrieved tables:

1. From the project page, click the project name.

Tip: Extensive Oracle documentation is available for broadening

your knowledge of database concepts and objects. For example, to

learn more about primary keys and constraints, see Oracle Database

Concepts. You can download Oracle documentation from:

http://www.oracle.com/technology/documentation/index

.html

Review Your Retrieved Objects

Migrating a Microsoft Access Application 3-13

From the project page, click Tables.

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

For each Microsoft Access table, the Tables page shows:

■ Oracle Table - Identifies the corresponding Oracle table, which defaults to the

Microsoft Access table name in all capital letters.

Note that the name may differ from the original one because of the collision

management facility in Exporter tool. For information about naming

guidelines and restrictions, click Help in Oracle SQL Developer and go to the

Frequently Asked Questions section.

If the Microsoft Access object was not successfully migrated to Oracle, then

this field will not have a corresponding Oracle table name. Instead, it will

contain a link to a page where you can create a corresponding Oracle table.

■ Primary Key - Indicates if a primary key exists for the table.

A table without a primary key is considered invalid in Application Migration.

You can create a primary key at this point. All tables you want to migrate

should have a primary key.

■ Foreign Key - Indicates if a foreign key exists for the table.

If you know a relationship exists between two tables, you should create a

foreign key. You can do this in Object Browser by creating a Foreign Key

Constraint. For more information, see "Managing Database Objects with

Object Browser" Oracle Application Express SQL Workshop Guide.

■ UI Defaults - Indicates if user interface defaults are set for the table.

Review Your Retrieved Objects

3-14 Oracle Application Express Application Migration Guide

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.

■ Status - Table status as either Valid or Invalid.

Only valid tables can be included in the migration.

3. To create a table:

a. On the Tables page, click the link in the Oracle Table column for the table you

want to create.

The Object Browser opens.

b. Click the Create button.

c. From the list of object types, select Tab le .

d. Follow the on-screen instructions.

4. To add a primary key:

a. On the Tables page, click the Oracle table name.

b. From the Tasks list, click Create Primary Key.

c. For Constraint Details, fill in the information.

d. Click Next.

e. Confirm the information and click Finish.

5. To add an index:

a. On the Tables page, click the Oracle table name.

b. From the Tasks list, click Create Index.

c. Select the type of index you want to create on this table.

Tip: To review the list of existing columns or constraints, expand the

Existing Columns or Existing Constraints regions.

Review Your Retrieved Objects

Migrating a Microsoft Access Application 3-15

For indexing NUMBER, VARCHAR, and DATE, select Normal. For indexing CLOB

columns, select Text.

d. Click Next.

e. For Index Definition, fill in the information.

The following graphic shows the fields to fill out if you selected Normal as the

type of index.

f. Click Next.

g. Confirm the information and click Create.

6. To set user interface defaults:

a. On the Tables page, click the Oracle table name.

b. From the Tasks list, click UI Defaults.

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

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

Tip: To review the list of existing table indexes or columns, click the

Table Indexes or Table Columns links.

Review Your Retrieved Objects

3-16 Oracle Application Express Application Migration Guide

The Table Dictionary page appears. Note that you are now working within

SQL Workshop. SQL Workshop is a 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.

e. On the Table Dictionary report, click the Oracle table name in the Object Name

column.

The Table and Column Properties page appears, listing column information as

it will appear in forms and reports.

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

The Column Defaults page appears. You can update the column label, change

the sequence of how the columns appear by default, and so on.

g. Click Apply Changes to save your updates.

7. To include tables in the migration, select them in the left column.

8. Click Apply Changes to save your selections.

Reviewing Retrieved Queries

Next, review the queries retrieved from the Microsoft Access export. Application

Migration identifies invalid queries and 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.

For each Microsoft Access query, the Queries page shows:

■ Oracle View - Indicates the corresponding Oracle view, which defaults to the

Microsoft Access query name in all capital letters.

See Also: "Reviewing Retrieved Queries" on page 3-16, "Review

Your Retrieved Objects" on page 3-12, and "How to Migrate a

Microsoft Access Application" on page 3-1

Review Your Retrieved Objects

Migrating a Microsoft Access Application 3-17

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 is either Valid or Invalid.

Only valid queries can be included in the migration.

■ UI Defaults - Indicates if user interface defaults are defined 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:

a. 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.

b. Click the Compile button.

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

d. Follow the on-screen instructions.

4. To edit a query:

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

b. 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.

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

query.

d. Update the query and recompile it.

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

f. 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:

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

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

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

d. On the Create Defaults page, click Create Defaults.

The Table Dictionary page appears. The Table Dictionary page appears. Note

that you are now working within SQL Workshop. SQL Workshop is a

component of Oracle Application Express with utilities to load and unload

Review Your Retrieved Objects

3-18 Oracle Application Express Application Migration Guide

data from an Oracle database, generate DDL, view object reports, and restore

dropped database objects.

e. To edit the information, click the object name.

The Table and Column Properties page appears.

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

The Column Defaults page appears. You can update the column label, change

the sequence of how the columns appear by default, and so on.

g. Click Apply Changes to save your updates.

6. To include queries in the migration, select the Include check box in the left column

on the Queries page.

7. Click Apply Changes to save your selections.

Reviewing Retrieved Forms

Next, review the forms retrieved from the Microsoft Access export. Application

Migration identifies invalid forms and lists additional information, such as the form’s

source type and source name.

For valid forms with a source type of table, you can select the type of object you want

the form to become within Oracle Application Express: form (default), report and

form, or tabular form.

Microsoft Access forms based on a query are migrated to Oracle Application Express

forms. Microsoft Access forms based on a SQL query are migrated to Oracle

Application Express reports.

After you update the forms, select the ones you want to include in the migration.

To review retrieved forms:

1. From the project page, click Forms.

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

See Also: "Reviewing Retrieved Forms" on page 3-18, "Review Your

Retrieved Objects" on page 3-12, and "How to Migrate a Microsoft

Access Application" on page 3-1

Review Your Retrieved Objects

Migrating a Microsoft Access Application 3-19

For each Microsoft Access form, the Forms page shows the following information,

if available:

■ Source type:

– Table - The Oracle table that was migrated from the Microsoft Access

table.

– Query - The Oracle view that was migrated from the Microsoft Access

query.

– SQL Query - The original Microsoft Access SQL query that the Microsoft

Access form is based on. Note that this query has not been parsed.

Therefore, you must edit it to make it valid Oracle SQL syntax.

– Nothing - The form has no underlying source type.

■ Source name - The Oracle table or view name if the source type is a table or

query.

■ Status - Form status as Valid or Invalid. The source of the form must have a

status of Valid before you can select it for migration.

A form’s status is based on two factors: status of its underlying source object

and inclusion of the source object in the migration. Specifically, a form has a

status of valid if either one of these situations exists:

– Its Source Type object (table, query, or SQL query) is valid, and it has been

included in the migration. Its check box is enabled and can be selected.

– Its Source type object has a status of valid, but the source object was not

included in the migration. Its check box is disabled.

A form has a status of invalid if either one of these situations exists:

– No Source Type is listed. Its check box is disabled.

– Its Source Type object (table, query, or SQL query) is invalid. Its check box

is disabled.

■ Startup form - Identifies the form that displays when you open your Microsoft

Access database.

■ Parent form - Indicates the form and subform relationship that exists in your

Microsoft Access database. For example, the CallListSub form shows

Contacts as its Parent Form.

■ Migrate to: Form, Tabular Form, or Report and Form - The select list appears if

the source type is a valid table.

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

Attempt to compile invalid SQL queries at the bottom of the page.

Using this option can validate some SQL queries that show a status of invalid.

Note that SQL queries from Microsoft Access forms are not loaded into the

Exporter tool, and are therefore not parsed.

3. To edit a SQL query:

a. On the Forms page, click the SQL Query you want to edit.

b. Click Validate to find the invalid part of the SQL query.

c. Update the query and validate it.

d. When it is validated, click the project name breadcrumb.

Review Your Retrieved Objects

3-20 Oracle Application Express Application Migration Guide

To include the validated query, click Forms on the project page to go to the

Forms page. Then select the newly validated query in the left column and click

Apply Changes.

4. To edit a query:

a. On the Forms page, click Query for the form you want to edit.

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

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

query.

d. Update the query and recompile it.

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

f. To include this validated query, select it in the left column on the Queries page

and click Apply Changes.

5. To review details about a form, click the link in the Access Form column.

6. To include forms in the migration, select the Include check box in left column on

the Forms page.

7. Click Apply Changes to save your selections.

Reviewing Retrieved Reports

Next, review the reports retrieved from the Microsoft Access export. Application

Migration identifies invalid reports and lists additional information, such as the

report’s source type and source name.

After you update the reports, select the ones you want to include in the migration. To

include a report, the source of the report must have a status of Valid.

To review retrieved reports:

1. From the project page, click Reports.

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

See Also: "Reviewing Retrieved Reports" on page 3-20, "Review

Your Retrieved Objects" on page 3-12, and "How to Migrate a

Microsoft Access Application" on page 3-1

Review Your Retrieved Objects

Migrating a Microsoft Access Application 3-21

For each Microsoft Access report, the Reports page shows the following

information, if available:

■ Source type:

– Table

– Query - The Oracle view that was migrated from the Microsoft Access

query.

– SQL Query - The original Microsoft Access SQL query that the Microsoft

Access form is based on. Note that this query has not been parsed.

Therefore, you must edit it to make it valid Oracle SQL syntax.

– Nothing - The report has no underlying source type.

■ Source name

■ Status of the report: Valid or Invalid. The source of the report must have a

status of Valid before you can select it for migration.

A report status is based on two factors: status of its underlying source object

and inclusion of the source object in the migration. Specifically, a report has a

status of valid if either one of these situations exists:

– Its Source Type object (table, query, or SQL query) is valid, and it has been

included in the migration. Its check box is enabled and can be selected.

– Its Source type object has a status of valid, but the source object was not

included in the migration. Its check box is disabled.

A report has a status of invalid if either one of these situations exists:

– No Source Type is listed. Its check box is disabled.

– Its Source Type object (table, query, or SQL query) is invalid. Its check box

is disabled.

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

Attempt to compile invalid SQL queries at the bottom of the page.

Using this option can validate some SQL queries that show a status of invalid.

Note that SQL queries from Microsoft Access forms are not loaded into the

Exporter tool and are therefore not parsed.

Generate the Oracle Application Express Application

3-22 Oracle Application Express Application Migration Guide

To edit a SQL query:

a. On the Reports page, click the SQL Query link you want to edit.

b. Click Validate to find the invalid part of the SQL query.

c. Update the query and validate it.

d. When it is validated, click the project name breadcrumb.

e. To include the validated query, click Reports on the project page to go to the

Reports page. Then select the newly validated SQL query in the left column

and click Apply Changes.

4. To edit a query:

a. On the Reports page, click Query for the report you want to edit.

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

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

query.

d. Update the query and recompile it.

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

f. To include this validated query, select it in the left column on the Queries page

and click Apply Changes.

5. To review details about a report, click the link in the Access Report column.

6. To include reports in the migration, select the Include check box in left column on

the Reports page.

7. Click Apply Changes to save your selections.

Reviewing Database, Module, and Pages Information

From the project page, you can drill down to see information about the database,

modules, and pages for the migration project.

■ Database - Displays summary information about the Microsoft Access database,

including the full path and size of the .mdb file.

■ Modules - Displays the Visual Basic Code, enabling you to extract embedded SQL

statements for you to use or edit in your Oracle Application Express application.

■ Pages - Displays information for reference purposes.

Generate the Oracle Application Express Application

After validating and updating objects, you now must generate the application in

Oracle Application Express. You can create an application based on valid forms and

reports, or a maintenance application based on valid tables and views.

See Also: "Reviewing Database, Module, and Pages Information" on

page 3-22, "Review Your Retrieved Objects" on page 3-12, and "How to

Migrate a Microsoft Access Application" on page 3-1

See Also: "Generate the Oracle Application Express Application" on

page 3-22 and "How to Migrate a Microsoft Access Application" on

page 3-1

Generate the Oracle Application Express Application

Migrating a Microsoft Access Application 3-23

When creating an application, a home page is defined by default. You have the option

to create additional blank pages so that you can introduce further navigation

possibilities.

You can then choose which user interface theme your application should be based on.

By default, the application uses one level of tabs.

As a shortcut, you can also set some application defaults. These defaults are used

whenever you create new applications.

Setting Up Application Defaults

To set up application defaults (optional):

1. On the right side of the project page, click Set Application Defaults in the Tasks

list.

2. Select the options you want to use as defaults.

For more information, see item Help. Clicking the item label opens a separate

window describing the item and its options.

3. Click Apply Changes.

The project page appears.

Generating Applications

To generate either type of application:

1. On the right side of the project page, click one of the following in the Tasks list:

■ Generate Application - This option generates an application based on the

forms and reports you selected to include.

■ Generate Maintenance Application - This option generates an application

based on the tables and queries you selected to include.

2. In the Selected Application Objects section, you can customize specific pages.

For example:

■ To rename a page, click the page name link and enter the new name on the

New Page Definition page that appears.

■ To select the type of navigation on the application’s home page, click the

Home Page link.

On the New Page Definition page that appears, select Vertical Unordered List

with Bullets, Vertical Images List, or Horizontal Images List.

■ To display an image on a parent page, click the page link.

On the New Page Definition page that appears, go to the Page Icon field and

select the image you want to appear on that page. You can either select an

image from the select list or click the Find icon (flashlight) to open a page of

options.

Repeat this step for each parent page. If you do not explicitly select an image

for a page, the default image appears for that page.

Note that for the image to appear in your application, you must have selected

either Vertical Images List or Horizontal Images List for the Home page

navigation.

3. To add a blank page to the application, scroll down to the Add Page section and

click Add Page.

Deleting a Migration Project

3-24 Oracle Application Express Application Migration Guide

The new page appears at the bottom of the list in the Selected Application Objects

section.

4. Click Next.

5. On User Interface, select a theme for the application.

A theme is a collection of templates that define the layout and style of an

application, including buttons and pages.

Options include:

a. Show - Select the type of themes to display.

b. Select a Theme - Select a theme from the list.

c. Click Next.

6. Confirm your selections and click Create.

7. To preview the application, click Run Application.

8. Log in using your Oracle Application Express workspace credentials.

Your application now appears as a separate application in Oracle Application

Express.

9. To customize your application, scroll down to the Developer toolbar and click Edit

Application.

You might want to do the following customizations immediately after you

generate your application:

■ Rename the application. Each application has a unique ID, but the migration

project name becomes the application name by default. To more easily identify

an application, you might want to change its name to something more

meaningful by editing the application attributes.

■ Change the authentication scheme. By default, the authentication scheme is

Application Express authentication. You can change this by editing the

application attributes.

Deleting a Migration Project

When you delete a migration project, you delete only the metadata associated with the

migration project. Deleting a migration project does not delete or impact applications

you have generated from the project or any objects, such as tables or views, in the

schemas associated with your workspaces.

To delete a migration project:

1. Log in the workspace you created for your conversion project as described in

"Logging In To Your Oracle Application Express Workspace" on page 3-8.

2. Click the Application Builder icon.

3. Click Migrations on the right side of the page.

4. On the Application Migrations page, click the project name.

5. On the Tasks list, click Delete Project.

See Also: Oracle Application Express Application Builder User's Guide

to learn more about editing application attributes, adding pages,

deploying your application, and so on.

Deleting a Migration Project

Migrating a Microsoft Access Application 3-25

Click the Delete Project button and confirm the deletion.

Deleting a Migration Project

3-26 Oracle Application Express Application Migration Guide

Converting an Oracle Forms Application 4-1

Converting an Oracle Forms Application

This section describes the steps to convert applications from Oracle Forms to Oracle

Application Express.

Topics:

■ How to Convert Your Application

■ Convert Oracle Forms to XML

■ Create a Workspace and Add Oracle Application Express Users

■ Upload Database Objects into the Schema Associated with Your Workspace

■ Create a Conversion Project

■ Review and Edit Forms Metadata

■ Generate the Oracle Application Express Application

■ Deleting a Migration Project

How to Convert Your Application

Before You Begin

To convert applications from Oracle Forms to Oracle Application Express, you must

perform the steps described in this section. The following graphic outlines the entire

process.

Convert Oracle Forms to XML

4-2 Oracle Application Express Application Migration Guide

The conversion process consists of the following steps:

Convert Oracle Forms to XML

Create a Workspace and Add Oracle Application Express Users

Upload Database Objects into the Schema Associated with Your Workspace

Create a Conversion Project

Review and Edit Forms Metadata

Generate the Oracle Application Express Application

Convert Oracle Forms to XML

In order to convert Oracle Forms to Oracle Application Express, you must convert the

Forms modules to formats the Create Migration Project wizard can consume.

Topics:

■ Converting FormModules, ObjectLibraries, or MenuModules to XML

■ Converting a PL/SQL Library

■ Converting an Oracle Report to XML

Converting FormModules, ObjectLibraries, or MenuModules to XML

You can convert Oracle Forms FormModule, MenuModule, and ObjectLibrary files to

Extensible Markup Language (XML) using the Oracle Forms to XML conversion tool,

Forms2XML.

Topics:

■ About the Forms2XML Conversion Tool

Important: You must follow the steps in the exact sequence

presented in this section.

Convert Oracle Forms to XML

Converting an Oracle Forms Application 4-3

■ Using the Forms2XML Conversion Tool From a Command Line

■ Using the Forms2XML Conversion Tool in a Java Program

About the Forms2XML Conversion Tool

The Oracle Forms to XML conversion tool, Forms2XML was introduced in Oracle9i

Oracle Developer Suite and is also available in Oracle Developer Suite 10g. This tool

should also work on files from earlier releases of Oracle Forms. If the Oracle Forms to

XML Conversion tool does not work for a specific file from an earlier version of Oracle

Forms, then you must upgrade the file to Oracle Developer Suite 9i or 10g and then

convert to XML

Forms2XML produces an XML file that has the same base name as the Forms file with

an .xml extension. The extension _fmb, _mmb, or _olb is added to the base file name

to indicate whether the original file was a Forms Modules, a MenuModule, or an

Object Library. The following table displays a few examples of the changes the tool

makes to the name of the file being converted:

Using the Forms2XML Conversion Tool From a Command Line

The Forms2XML command takes one or more Forms Module files as an argument. The

files can be FormsModule (.fmb), ObjectLibrary (.olb), or MenuModule (.mmb) files.

The output is placed in the current folder. The command has the following syntax:

frmf2xml [options] file1 [file2...]

java oracle.forms.util.xmltools.Forms2XML [options] file1 [file2...]

Available options are described in the following table.

Note: The Forms2XML utility must generate an XML file in English

only. If the generated XML tags are not in English, then the file will

fail to load. The conversion of synonym-based data blocks is not

supported. Only data blocks based on tables or views is supported by

the conversion process. Please ensure that the application you wish to

convert is based only on tables and views.

File name before conversion File name after conversion

myForm.fmb myForm_fmb.xml

myMenu.mmb myMenu_mmb.xml

myLibrary.olb myLibrary_olb.xml

Options Description

USE_PROPERTY_IDS=YES/NO Setting USE_PROPERTY_IDS to YES causes the

Forms to XML conversion tool to write the

internal ID for Real or Character into the XML

file.

The default value (NO) generates property

value names as before.

Note: The Forms2XML utility must be run

with USE_PROPERTY_IDS=NO.

Convert Oracle Forms to XML

4-4 Oracle Application Express Application Migration Guide

Using the Forms2XML Conversion Tool in a Java Program

You can pass the Forms2XML tool a JdapiModule Java object (class

oracle.forms.jdapi.JdapiModule) and dump its objects and properties to an

XMLDocument (class oracle.xml.parser.v2.XMLDocument). For example:

...

// get the modules in the session

JdapiIterator mods = Jdapi.getModules();

// ... and iterate round them

while(mods.hasNext())

{

JdapiModule mod = (JdapiModule)mods.next();

Forms2XML converter = new Forms2XML(

XMLDocument doc = converter.dumpModule(false);

...

}

The Boolean argument taken by the dumpModule() method instructs the converter to

dump all properties to the XMLDocument doc, not just those which are overridden.

Once you have the module as a XMLDocument object, you could then use the XDK

classes to manipulate it programmatically. For example, you could traverse it, remove

or add objects, change properties, copy objects into other modules that are also saved

as XMLDocument objects, and so on.

To learn more about Oracle Forms, see:

http://www.oracle.com/technetwork/developer-tools/forms/overview/index.

html

Converting a PL/SQL Library

To convert a PL/SQL library, use the File > Convert option in Oracle Forms

Builder to convert PL/SQL library .PLL files to .PLD text files.

To learn more about Oracle Forms, see:

http://www.oracle.com/technetwork/developer-tools/forms/overview/index.

html

OVERWRITE=YES/NO Valid values are YES or NO.

If OVERWRITE=YES, the tool overwrites any

XML files with the same name that exist in the

output directory.

If OVERWRITE=NO, (default) the tool does not

overwrite any XML files with the same name

that exist in the output directory. The tool will

stop processing and return the message:

If the file file exists, use argument

OVERWRITE=YES to replace.

DUMP=ALL/OVERRIDEN If DUMP=ALL, the tool dumps all properties.

If DUMP=OVERRIDEN, the tool dumps only

those properties that are overridden.

Options Description

Create a Workspace and Add Oracle Application Express Users

Converting an Oracle Forms Application 4-5

Converting an Oracle Report to XML

To convert an Oracle Report, use the File Conversion option in Reports Builder to

convert binary (.RDF), ASCII (.REX), and .JSP reports to XML format.

To learn more about Oracle Reports, see:

http://www.oracle.com/technetwork/middleware/reports/overview/index.htm