Converting FormsBy David Peake
Modernize Oracle Forms applications with Oracle Application Express.
Oracle Forms has been around since before the days of client/server computing, and numerous Oracle Forms applications have been faithfully meeting business requirements for many years.
Converting Oracle Forms applications to use another development tool is a labor-intensive, time-consuming process, and recent rumors that Oracle will soon drop support for Oracle Forms are unfounded. With these things in mind, why would anyone want to convert their Oracle Forms applications to use another development tool?
Typically, Oracle Forms modernization projects convert legacy applications to use the latest Oracle Database version and enable developers to satisfy user demands for greater user interactivity and Web 2.0 capabilities. A modernization option for Oracle Forms applications that will deliver on both of these objectives is Oracle Application Express.
Understanding the Conversion Process
The capabilities for converting from Oracle Forms to Oracle Application Express in Oracle Application Express 3.2 generate an initial application design that you then enhance and expand to fully replicate the original application. The conversion maps blocks to pages and various item types, but it does not convert business logic stored in Oracle Forms triggers, program units, or libraries. After you generate the initial design, you can take full advantage of the rapid application development capabilities in Oracle Application Express to enhance the generated pages. Figure 1 describes the process flow of an application migration from Oracle Forms to Oracle Application Express.
For this column, we will convert some components from the Oracle Forms Summit sample application. In particular, we are going to use the customers.fmb, customers.mmb, orders.fmb, stndrd20.olb, and wizard.pld files. The modified script files to create the required tables, together with the XML file conversions for each of the source files, are available at oracle.com/technetwork/issue-archive/2009/09-may/o39browser-132925.zip. The steps in this column assume that you have an Oracle Application Express 3.2 workspace defined and have downloaded and extracted the o39browser-132925.zip file.
Converting Oracle Forms Source to XML
Although the XML file equivalents for each of the Oracle Forms source files are already included in the download for this column, you should know how to perform this conversion on your own. The Oracle Forms-to-XML conversion tool, Forms2XML, produces an XML file that has the same base name as the Oracle Forms file but with an .xml extension. For example, stndrd20.olb becomes stndrd20_olb.xml. The command has the following syntax:
frmf2xml [options] file1 [file2...] java oracle.forms.util.xmltools.Forms2XML [options] file1 [file2...]
Note that you can generate your own XML files for the sample application by using Forms2XML and the Oracle Forms files in the complete Summit application download. (Forms2XML was first introduced in Oracle9i Internet Developer Suite.)
Creating the Conversion Project
For an Oracle Application Express project to load Oracle Forms files, it must be associated with the schema where the underlying tables are defined. The following steps use files contained in the sample application download for this column and describe how to create the Summit sample application tables:
1. From the Oracle Application Express home page, select SQL Workshop -> SQL Scripts -> Upload .
2. Click Browse , select the Summit_DDL.sql file, click Open , and click Upload .
3. From the SQL Scripts page, click Upload , click Browse , select the Summit_Data.sql file, click Open , and click Upload .
4. Click the Summit_DDL.sql file; click Run ; and then from the Run Script page, click Run again.
5. From the Manage Script Results page, click Go until Status shows Complete .
6. Click the View Results icon. Note that the value for Statements Processed and Successful should be 58 .
7. Click the SQL Scripts locator link.
8. Click the Summit_Data.sql file, and click Run ; then from the Run Script page, click Run again.
9. From the Manage Script Results page, click Go until Status shows Complete .
10. Click the Show Results icon. Note that the value for Statements Processed and Successful should be 1173 .
With the database tables created, use the XML files generated by Forms2XML (and included with the sample application) and the following steps to create the conversion project:
1. From the Oracle Application Express home page, click Application Migrations (on the right within Migrations ).
2. Click Create Project .
3. Enter Summit for Project Name , click Browse , select the customers_fmb.xml file, click Open , and click Next .
4. Click Upload Another File , click Browse , select the orders_fmb.xml file, click Open , and click Upload .
5. Click Upload Another File , select PL/SQL Library (.PLD) from the File Type list, click Browse , select the wizard.pld file, click Open , and click Upload .
6. Click Upload Another File , select Forms Menu (_mmb.XML) from the File Type list, click Browse , select the customers_mmb.XML file, click Open , and click Upload .
7. Click Upload Another File , select Object Library (_olb.XML) from the File Type list, click Browse , select the stndrd20_olb.XML file, click Open , and click Upload .
8. Click Finish .
Figure 2 shows the newly created Oracle Forms conversion project.
Reviewing and Analyzing Your Project
Once you have created your migration project, you can review the components and determine which user interface components to generate. The project enables you to quickly drill down into any of the Oracle Forms components, including blocks, items, triggers, lists of values (LOVs), and program units.
A block query is based on analysis of the corresponding SQL and postquery triggers from the form’s block. The following steps show how to modify an Oracle Application Express query that will be generated from an Oracle Forms block:
1. From the Project: Summit page ( Home -> Application Migrations -> Project: Summit ), under File Name , click orders_fmb.xml to go to the Forms Objects page.
2. Click the Blocks link, and under Name , click S_ORD to go to the Block Details page.
3. Within the Application Express Page Query box, select Custom Query from the Use Query list.
4. Click the copy icon under Enhanced Query .
5. Modify the SQL in the Custom Query text box as required. Note that for this exercise, no modifications are needed.
6. Click Apply Changes at the top of the page.
The following steps show how to select or deselect blocks and items to be included in the generated application and how to change titles and labels:
1. From the Project: Summit page, under Blocks , click 4 (to go to the blocks defined on the Orders Form).
2. Under Include , uncheck the S_ITEM block.
3. Change the name of the S_ORD block to Orders and the S_INVENTORY block to Inventories .
4. Click Apply Changes .
5. Under Item Count , click 9 (to go to the items defined on the Orders [S_Ord] block).
6. Change Item Prompt for Customer Id to Customer and Item Prompt for Sales Rep Id to Sales Representative .
7. Click Apply Changes .
To facilitate project management of the application conversion, each component can be annotated with the following types of information:
Applicability is used to indicate whether a particular component needs to be analyzed and reimplemented after the converted application is generated. For example, most Oracle Forms key triggers such as Key-Clrblk and Key-Commit are not applicable in an Oracle Application Express application, because such functionality is generated by default.
The following steps show how to annotate a trigger:
1. From the Project: Summit page, under Triggers , click 45 (to go to the triggers defined on the Orders Form).
2. Click the Trigger Level column heading, and select Item , which will create a filter showing only the item-level triggers.
3. Click the Applicable column heading, and select Yes , which will create a filter showing only applicable triggers.
4. Click the edit icon for the When-Radio-Changed trigger.
5. Update annotations as required. For example, for Notes add,
This functionality will need to be implemented postgeneration as a validation,and for Tags add Validation .
6. Click Apply Changes .
Generating Your Application
Once your project review and analysis are complete, you can generate your Oracle Application Express application. During application generation, forms are converted to one or more pages, depending on the number of blocks included. The type of application pages generated is determined by the presence of relationships between the blocks, the number of records displayed, and whether INSERT or UPDATE are allowed. The following types of pages can be generated for a block: a report, a report and a form, or a master/detail page.
The following steps describe how to generate the Oracle Application Express Summit application:
1. From the Project: Summit page, click Create Application .
2. Leave Summit as the Name for the application, and click Next .
3. At this point, you can modify user interface details (by clicking Page Name ) as well as remove unwanted pages or add more pages. For this exercise, no modifications are needed, so click Next .
4. Select the desired theme, Theme 20 ; click Next ; and click Create .
5. Click Run Application .
Once you have generated your Oracle Application Express application, you will need to perform postgeneration modifications to incorporate business logic, using validations, processes, and computations. Review of and enhancements to the generated user interface may also be required to further improve usability. Figure 3 shows the Summit application Orders detail page after postgeneration cleanup.
Oracle Forms conversion in Oracle Application Express is not designed to fully replicate an Oracle Forms application. The conversion process does, however, offer significant benefit by generating user interface components and providing project management tracking capabilities.
The effort and resources required for each Oracle Forms conversion project are largely dependent on the size, complexity, and business logic implementation within the Oracle Forms application. I strongly recommend that you undertake a prototype or a proof of concept to properly plan each Oracle Forms conversion project before you begin.
David Peake (email@example.com) is a principal product manager in Oracle’s Server Technologies division. He has been with Oracle since 1993.