Migrating to Oracle Application Express
By Scott Spendolini
Centralize and secure your one-off applications.
Oracle Application Express (formerly Oracle HTML DB) is a Web application development tool based on Oracle Database. Using nothing more than a Web browser, it lets you quickly and securely create and deploy Web applications. Oracle Application Express is supported in the standard and enterprise editions of Oracle Database 10g Release 1 and 2 and in Oracle9i Database Release 2 (188.8.131.52 or greater).
In this column, I'll show you how to migrate your Microsoft Access applications to Oracle Application Express.
Migrating from Microsoft Access
One of the most popular reasons customers cite for using Oracle Application Express is to migrate Microsoft Access applications to the Web and to Oracle Database. Using Oracle Application Express and the Oracle Migration Workbench (OMWB) can greatly streamline this migration.
There are four major steps to a successful migration: 1) analyze your application and data model; 2) design the new application; 3) migrate the data; and 4) migrate the application. I'll use a simple Microsoft Access application as the migration example and examine each of these steps. To follow along, download the sample Microsoft Access application. If you'd like to create the same application without using Microsoft Access or OMWB, you can use your installations of Oracle Application Express 2.0 and Oracle Database 10g and run projtrak.sql, which you can download at oracle.com/technology/oramag/oracle/05-nov/projtrak.sql.
This is the most critical step. The goal of the Analyze step is to determine what you need to do to the application in order to have a successful migration. This includes identifying any data model deficiencies, UI issues, heavy use of VBScript, and any other design issues.
No one knows how an application works better than its users and developers. Take time during this phase to get as much information from them as possible. Some sample questions for users: What screen do you use the most? The least? What do you like about the application? What would you change? Be sure to get a demo of the application because it's critical to understand how it works before you try to migrate it.
When talking to users, ask which application forms and tables they don't use. I often see Microsoft Access applications with several unused or outdated objects. Eliminating them up front will save you time in the overall migration. Also, make sure you understand what any VBScript and macros do, because you may have to rewrite this functionality in PL/SQL once the application is migrated to Oracle Application Express.
It's also critical that you begin to set users' expectations: Let them know the new application won't look and feel exactly like the existing application, but it will accomplish the same tasks.
The projtrak.mdb file includes two tables, three forms, one query, one report, and one macro. The two tables— PROJECTS and TASKS —are related through a foreign key, and both tables have AutoNumber columns. If you run the application in Microsoft Access, you can clearly see that the Main Menu form is intended for navigation, while the other two forms—Projects and Tasks—are for the master-detail relationship between the PROJECTS and TASKS tables. The data model is fairly solid in this case, so you don't need to make any changes.
In the Design step, you can start planning what your new data model and Oracle Application Express application will look like. Just as in the Analyze step, you should look at both the data model and application. Based on your interviews, you should be able to decide which tables to optimize and which to leave alone. You can also begin to map which components will need to be migrated. Finally, look at any VBScript that needs to be migrated and plan on rewriting it in PL/SQL or using Oracle Application Express components to achieve the same business rules.
Looking at the sample Microsoft Access application, you can eliminate the Main Menu form because it's only a navigational aid. You'll use tabs in Oracle Application Express to achieve the same result. Suppose that during the Analyze step you learned that a chart of the Projects by Hours report would help users present data more compellingly to management. You can easily create a number of charts with Oracle Application Express, so this chart is something you agree to build.
Migrate the Data
Thanks to OMWB (available at oracle.com/technology/migration), migrating the data from Microsoft Access to Oracle Database is simple. All you have to do is download and install OMWB, point it at your Microsoft Access *.mdb file, and follow the instructions. OMWB will take all of your table structures and data and re-create them in an Oracle schema. You can choose which object types to migrate, whether you want to move the data, and assign any data type mappings.
After you install OMWB, start by using OMWB's Exporter to extract the metadata of the Microsoft Access application. The Exporter is located in the msaccess_exporter folder in the directory where OMWB is installed. Choose and run the appropriate file based on the version of Microsoft Access you're migrating from. After you run the Exporter, locate your MDB file, select an output directory, and click Export Database Schema . This will generate an XML file in the specified directory.
Next, launch OMWB and follow the prompts through the Capture and Migration wizards, specifying the XML file that was just generated when prompted. You'll need access to a database account with DBA privileges to successfully complete the migration, because a new schema and tablespace will be created. For this example, you can simply accept all of the default values. When OMWB completes, you should have a PROJTRAK schema with two tables, two sequences, two triggers, six indexes, and one view.
If your Analyze and Design steps called for some table optimization, it's best to do this after the data has been migrated to Oracle Database. This way, you can preserve any newly generated key values since they'll be initially created on the Oracle side. Also, there is a wider variety of tools available on the Oracle side to help you with any data model changes you have to make.
Migrate the Application
The final step is to migrate the application, specifically the UI and the business rules. Oracle Application Express's built-in wizards will greatly expedite this step; they let you create a complete form and report in about a minute. I try to use the wizards as much as possible, and then tweak their results when necessary. (Note that the steps in this column were documented using Oracle Application Express 2.0.)
To get started, create (or have your Oracle Application Express administrator create) a new workspace, based on the newly created PROJTRAK schema. Once you sign into your new workspace, generate a new application using the Create Application Wizard. To start the wizard, from the Oracle Application Express home page, click the drop-down menu on the Application builder icon, and choose Create Application->Create Application . When prompted to add pages, add two blank pages (name them Projects and Project Details) to your application. Accept the default values for the remainder of the screens in the wizard.
Next, use Oracle Application Express's Master-Detail Wizard to build a report and associated forms based on the PROJECTS and TASKS table. Start by running Page 1 (click the traffic light icon) and then click Create in the Developer Toolbar at the bottom of the page. Choose Region on this page and click Next , choose Form and click Next , and then choose Master Detail and click Next . Select the PROJECTS (Master) table, add all of the columns to the Displayed Columns list, and click Next . Then do the same for the TASKS (Detail) table, and click Next . Notice how the list of possible Detail tables is limited to only those related to PROJECTS —in this case, just TASKS . Accept the default values when asked to define the Primary Key for both the Master and the Detail table, because OMWB created a trigger and sequence for each table. Click Next .
Accept the default values for the next two screens, Define Master Options and Choose Layout. For the Page Attributes screen, set the Master page to 1 and the Detail page to 2 (because you already created these pages when you created the application), and click Next . For tabs, choose Use an existing tab set and reuse an existing tab within that tab set and specify Tab Set TS1 , and click Next . Finally, choose T_PROJECTS: label="Projects" as the Tab Name, and click Next . Click Finish to create your Master-Detail report and form.
To enhance the application, add a chart based on the PROJECTS_BY_HOURS view. Navigate to and edit Page 1 in your application: run the page and click Create in the Developer Toolbar. Choose Region on this page and click Next , and then choose Chart and click Next . Choose Pie and click Next , and enter Projects by Hours for the title. Also, choose 2 for Column so that the chart displays next to the PROJECTS report, and click Next . Enter the following SQL:
select null link, projectname label, sumofhours value from projects_by_hours
Click Create Region to create your chart, and then run your application. At this point, you should have a working Oracle Application Express application, similar to Figure 1. Your application may look different depending on which theme you selected. This application has the same functionality as the Microsoft Access application. It's also centrally maintained and backed up, secure, and scalable, and requires nothing more than a Web browser on the client. Not bad for just a few minutes of work!
Before Oracle Application Express and the Oracle Migration Workbench were available, migrating from Microsoft Access to a Web-based Oracle solution could be a costly, cumbersome, and difficult undertaking. With the combination of these powerful tools, you can now do mission-critical migrations affordably and quickly.
Scott Spendolini is president of Sumner Technologies, LLC.