Converting Your Oracle Forms Applications to Application Express 3.2

Purpose

This tutorial shows you how to convert your Oracle Forms Applications (including Menus and Reports) to Oracle Application Express 3.2.

Time to Complete

Approximately 60 minutes

Topics

This tutorial covers the following topics:

 Overview
 Prerequisites
 Creating and Uploading Files into a Conversion Project
 Editing Your Conversion Project
 Reviewing and Analyzing the Objects in Your Project
 Adding More Files to Your Project
 Generating your Application Express Application
 Reviewing your Application Express Application
 Examining Your Conversion Project After Generation
 Summary
 Related Information

Viewing Screenshots

 Place the cursor over this icon to load and view all the screenshots for this tutorial. (Caution: This action loads all screenshots simultaneously, so response time may be slow depending on your Internet connection.)

Note: Alternatively, you can place the cursor over an individual icon in the following steps to load and view only the screenshot associated with that step. You can hide an individual screenshot by clicking it.

Overview

In Oracle Application Express 3.2, a new utility has been provided to allow you to convert your Oracle Forms, Menus, Reports, PL/SQL Libraries and Object Libraries to an Oracle Application Express application. The process to convert your Oracle Forms applications is outlined in the diagram below:

In order to convert your Oracle Forms application, you need to perform the following steps:

1.

Convert Oracle Forms to XML: Run the Forms to XML Conversion tool, Forms2XML, to convert the Forms FormModules in your application. In addition, this tool will convert OLBs (Object Libraries) and MMBs (Menus). This creates XML output files.

2.

Convert PLLs to PLD Text: Run the Convert utility in Forms Builder to convert PLLs to PLD text format.

3.

Convert Reports to XML: Run the Convert utility in Reports Builder to convert your Reports to XML format.

4.

Create an Oracle Application Express Workspace: Run Oracle Application Express and create a workspace. Associate the workspace with the Oracle Forms application schema.

5. Create a conversion project: Log in to the workspace created in Step 2 and navigate to Application Migrations from the Migrations Tasks region. Create a conversion project and load the application metadata from Step 1.
6. Analyze the Oracle Forms application: From the Application Migration Workshop, verify and adjust the Forms application metadata. Refine the scope of conversion.
7. Generate the Oracle Application Express Application: Create an application based on the selected Forms objects.
8. Customize your Application Express application: Open your application in the Application Builder part of Application Express, and customize your converted application. You can change application attributes or add new pages to the application.

Back to Topic List

Prerequisites

Before you perform this tutorial, you should:

1.

Install Oracle Database 11g.

2.

Install Oracle Application Express Release 3.2 located on OTN.

3. Download and unzip the forms_conversion.zip file into your working directory.
4. Create an Application Express Workspace and user.
5. In SQL*Plus, as your workspace user, execute the forms_conversion_ddl.sql file to create the forms database objects
6. In SQL*Plus, as your workspace user, execute the forms_conversion_data_insert.sql file to load the data into the forms database objects.

Back to Topic List

Creating and Uploading Files into a Conversion Project

In this section, you create and update a conversion project. Perform the following steps:

1.

Enter the following URL to log in to Oracle Application Express.

http://<hostname>:8080/apex

 

2.

To log in to Oracle Application Express, enter the following details, and click Login.

Workspace: <your workspace name>
Username: <your username>
Password: <your password>

 

3.

On the Workspace home page, under the Migrations region, click Application Migrations .

 

4.

Click Create Project.

 

5.

Enter a name for your Project. You will be converting an application from Oracle Forms. The default type is FORMS. Note that the other type is Access if you want to convert a Microsoft Access application. You need to have the FORMS schema loaded (from the Prerequisites section) and click Browse... for Forms Module XML file.

 

6.

Select customers_fmb.xml and click Open.

 

7.

Click Next. Make sure the schema where you created the forms database objects and loaded the data resides.

 

8.

Your customers_fmb.xml file has been uploaded. You need to upload a few more files. Click Upload Another File.

 

9.

Make sure that Forms Module (_fmb.XML) is selected for File Type and click Browse...

 

10.

Select orders_fmb.xml and click Open.

 

11.

Click Upload.

 

12.

Your orders_fmb.xml file has been uploaded. You need to upload one more file. Click Upload Another File.

 

13.

Make sure that PL/SQL Library (.PLD) is selected for File Type and click Browse...

 

14.

Select wizard.pld and click Open.

 

15.

Click Upload.

 

16.

Your wizard.pld file has been uploaded. You are now ready to create the project, click Finish.

 

17.

All the files have been uploaded. Although all the files are visible to the project page, most of the columns on the Interactive Report only relate to _fmb.xml files. For example, the Blocks, DB Blocks columns will always have values of 0 for all files except _fmb.xml files. You will review some of the objects for many of the files in later sections of this tutorial.

Note the Percent Complete column at the end of each row in this report. There are 2 components that determine this percentage: Applicablility and Completeness. If applicable is set to Yes then it is relevant to the conversion process and is evaluated for completeness. If applicable is set to No, then it is not relevant to the conversion process. If complete is set to Yes, the percent complete for this item is 100%. If applicable is set to Yes and complete is set to No, this item is relevant and the percent complete.

 

Back to Topic List

Editing Your Conversion Project

You may want to set certain defaults for your Conversion Project. Perform the following steps:

1.

Under the Tasks region, select Edit Project Details and Applicability.

 

2.

On the Project Details page, you can change the schema associated with the project, you can also associate a different APEX application (ID) that has already been generated. You can change this using the pop-up LOV. In the Component Applicability region, you determine whether a particular category is relevant to the users conversion process. In this case, you don't want alerts to be applicable for this conversion project. Select No for Alerts.

 

3.

Click the Trigger Applicability tab. Alternatively, if you are in the Show All mode, scroll down to the Trigger Applicability section.

 

4.

This section is similar to component applicability however is specific to triggers. Based on the trigger name and where the trigger was implemented within Oracle Forms, the table below will determine the applicability assigned to that trigger within the project. Click Apply Changes.

 

5.

Note that because you changed Alerts to not be applicable, the Percent Complete value changed. It is now higher because there are less objects that are relevant. Select the number link for alerts for the customers_fmb.xml file.

 

6.

Note that Applicable for both Alerts has been set to No. Click the Project:<project_name> breadcrumb. For example, in this case, Project:Forms Conversion OBE.

In the next section you will review the objects that will be generated when the application is created.

 

Back to Topic List

Reviewing and Analyzing the Objects in Your Project

In this section, you review and analyze the objects that were uploaded. Perform the following steps:

1.

From the Migration Project page, you can click each file to view the objects that were uploaded. You will view the Forms Objects first. Click the customers_fmb.xml file link.

 

2.

Select the component you want to view. In this case, you want to view Blocks. Select the Blocks link.

 

3.

A list of the blocks for the customers_fmb.xml file appear. You can include or not include blocks from this page. Notice that the navigation control blocks are not selected, because they don't have a Data Source Name, so they will not be generated when you create your Application Express Application. You can also change the title of the block when it is generated. Enter Customer Details for S_CUSTOMER1 and Maintain Customer in the title field for S_CUSTOMERand click Apply Changes.

 

4.

You want to review/edit the block information. Click the Name link of the S_CUSTOMER1 block.

 

5.

On the right side of the page, in the Block Status region, you see that this block will be converted into a Report and Form when the Application Express Application is created. Note that from this page, under Block Tasks, you can set applicability and completeness for this Block's items and triggers. In addition, you can navigate to another Block by clicking it's name in the list.

 

6.

On the left side of the window, you can view various information about the block. You can define specific annotations such as applicable, priority, complete and assign this block to a specific person on the development team to examine in closer detail or implement manually post-generation. In addition, the Notes section is automatically populated for many blocks with Incorporating Enhanced Query to identify that a POST_QUERY trigger has been interrogated and incorporated into the query that will be used when generating the application. Click Application Express Page Query. Alternatively, if you are in the Show All mode, scroll down to the Application Express Page Query section.

 

7.

When the Block is converted to a Report and Form in the Application Express Application, the query is enhanced by appending the POST-QUERY trigger logic in the query. You can change it to use the original query which is based on the database source item associated with the Block.

 

8.

Since you did not make any changes, click the Blocks breadcrumb. Note: There is currently an issue if you click Apply Changes and you haven't made any changes to the query.

 

9.

You can also customize the query where you can specify a custom query to be used in the generation of the current block. Select the S_CUSTOMER link.

 

10.

Select Custom Query for Use Query and then copy the following in the SELECT statement into the Custom Query area. Then click Apply Changes.

select  
                              
"S_CUSTOMER"."ID",
"S_CUSTOMER"."NAME",
"S_CUSTOMER"."PHONE",
"S_CUSTOMER"."ADDRESS",
"S_CUSTOMER"."CITY",
"S_CUSTOMER"."STATE",
"S_CUSTOMER"."COUNTRY",
"S_CUSTOMER"."ZIP_CODE",
"S_CUSTOMER"."CREDIT_RATING",
"S_CUSTOMER"."SALES_REP_ID",
"S_CUSTOMER"."REGION_ID",
"S_CUSTOMER"."COMMENTS",
"S_EMP"."FIRST_NAME"||' '||"LAST_NAME" "SALES_REP_NAME"
from "S_CUSTOMER" "S_CUSTOMER",
"S_EMP" "S_EMP"
where "S_EMP".ID (+) = "S_CUSTOMER".SALES_REP_ID

The above SQL changes the enhanced query into a regular join expression rather than inner select.

 

11.

You can view the items and exclude a non database item so that it is not included when the application is created. Select the number link for the Item Count for S_CUSTOMER.

 

12.

Deselect the include/exclude checkbox for SALES_REP_NAME and change the Item Prompts for Phone to Telephone No. and Name to Customer Name. Click Apply Changes.

 

13.

Click your Project breadcrumb to return to the main Project list of files.

 

14.

Under the Triggers column, select the number link for the orders_fmb.xml file.

 

15.

Select the Trigger Name header and scroll down in the list to select WHEN-VALIDATE-RECORD.

 

16.

Click the Edit icon in front of the WHEN-VALIDATE-RECORD row.

 

17.

Notice that this validation should check to make sure that the order date is before the ship date. This validation does not get generated however you can annotate it to specify what tasks need to be completed after the application is generated and by whom.

Applicable refers to whether the component is important (Yes) or can be ignored (No). In this case the trigger is very important so Applicable should be left as Yes.

Complete indicates that the component has been implemented into the Application Express application. In this case, you only identify that the work needs to be performed post-generation. Therefore, complete is set to yes only after the developer has implemented the validation. In this case, Complete should be left as No.

Select a different user for Assignee and add a Note to the assignee as to why he/she was assigned to work on that object.

You can also set a tag which will allow you to search for all the objects with a particular tag. You will assign a tag to this trigger so that after your application is generated and you implement this trigger as a validation in Application Express, you can update the trigger as complete. Enter VALIDATION in the Tags field and click Apply Changes.

 

18.

The change has been processed. Click the Project breadcrumb.

 

19.

You want to review the blocks in the orders_fmb.xml file. Select the number of blocks link for orders_fmb.xml.

 

20.

Select the S_ORD block link.

 

21.

Notice in the Block Status region that this block will be converted as a Master Detail form.

 

22.

For Master-Detail conversions, the 'Application Express Page Query' region is not visible on the Block Details page since two tables are passed in rather than a query. Select the Blocks breadcrumb.

 

23.

Select the Item Count link for the S_ORD block.

 

24.

The list of Block Items appear. Notice the Item type column identifies the original block item type. When an item is generated, Application Express will attempt to convert the item type to an equivalent Application Express item type. For example, the Payment Type column will be generated as a radio group. Click the Project breadcrumb.

 

26.

You want to also review the PL/SQL Library. Select the wizard.pld file link.

 

27.

Click the Annotations tab. Alternatively, if you are in the Show All mode, scroll down to the Annotations section.

 

28.

Change Priority to 4, enter Implement buttons to replicate wizard functionality on Page 0 for Notes, and enter BUTTON, PAGE 0 for Tags. Then click Apply Changes.

In the next section, you add some additional files (Menu, Report and Object Library) and review the files.

 

Back to Topic List

Adding More Files to Your Project

In this section, you add some additional files such as Form Menu, Report and Object Library to your project. Perform the following steps:

1.

From the Project page, click Upload File.

 

2.

Select Forms Menu (_mmb.XML) for File Type and click Browse...

 

3.

Select customers_mmb.xml and click Open.

 

4.

Click Upload and Upload Another.

 

5.

The customers_mmb.xml file has been uploaded. You need to upload a few more files. Select Oracle Report (.XML) for File Type and click Browse...

 

6.

Select Employees.xml and click Open.

 

7.

Click Upload and Upload Another.

 

8.

The Employees.xml file has been uploaded. You need to upload one more file. Select Object Library (_olb.XML) for File Type and click Browse...

 

9.

Select stndrd20_olb.xml and click Open.

 

10.

Click Upload.

 

11.

Select the Employees.xml link.

 

12.

The details for the report are displayed. The Report will be converted to an Interactive report, based upon the SQL Query, when the Application Express Application is generated. Click the SQL Query tab.

 

13.

The SQL query used to create the report is displayed. Click the Project breadcrumb.

 

Back to Topic List

Generating Your Application Express Application

In this section, you generate an application based on the conversion project. Perform the following steps:

1.

Before you create your application, you can set the application defaults. From Migration Project page, on the Tasks menu, click Set Application Defaults.

 

2.

You can set the Tabs, Authentication and Theme that will be used when the application is created. Select the Theme tab.

 

3.

Change the Theme to Theme 20 and click Apply Changes.

 

4.

From the Project window, click Create Application.

 

5.

There are two options when creating an application from a Migration Project. The first option is to base the application on a Migration Project. The second is to base the application on an existing application design model. This second option is useful when implementing a phased approach to migration where you have several applications you want to convert at different times. In this case, you want to create an application based on the first option. Select Based on Migration Project for Create Application and click Next.

 

6.

You see the list of pages that will be generated. You can add, modify or delete pages from this wizard page. Select the Employees page name link.

 

7.

You want to change the icon that will be displayed for this page. Select the flashlight next to Page icon.

 

8.

Select the biz_users_bx_128x128.png icon.

 

9.

The new icon has been inserted. Click Apply Changes.

 

10.

Click Next.

 

11.

Notice that Theme 20 is already selected because you set this theme in the application defaults. Click Next.

 

12.

Click Create.

 

13.

Your application was created successfully. Click the Run Application icon.

 

Back to Topic List

Reviewing Your Application Express Application

In this section, you review the resulting application. Perform the following steps:

1.

Enter your username and password and click Login.

 

2.

The Home page is displayed. The default template is Horizontal Images with Label List. Select the Customer Details image.

 

3.

An interactive report is displayed. You can navigate to the form by clicking one of the edit icons at the beginning of a row. In this case, you want to select the Home breadcrumb to return to the menu.

 

4.

Select the Maintain Customer image.

 

5.

The interactive report is displayed. The query behind this report comes from the custom query. Notice that Sales Rep Name shows the First Name and Last Name of the customer concatenated. To update the information in this report, click the edit icon in front of one of the records.

 

6.

A record is shown. Notice that the Customer Name item has an asterisk as it is mandatory, dates have a date picker associated, there is a select list for Credit Rating, and that the Comments uses a HTML Editor.  Notice that Sales Rep Name is not included because you excluded that block item in the S_CUSTOMER1 block. To see what validations were created, click the Edit Page link in the developer toolbar.

 

7.

Notice that a not null validation was created for Name and the Sales Rep ID and Region ID must be a number. Since you don't know the Sales Rep IDs for all Sales Representatives and you have this field on multiple forms, you will create a shared LOV to show the list of Sales Representatives and then create a Select List on the Maintain Customers page that shows the LOV. Select the Shared Components icon.

 

8.

Under User Interface, select List of Values.

 

9.

Click Create.

 

10.

Click Next.

 

11.

Enter SALES_REPRESENTATIVES for the Name and select Dynamic for the Type and click Next.

 

12.

Enter the following SELECT statement in the Query area and click Create List of Values.

                               
select distinct e.FIRST_NAME||' '||e.LAST_NAME display_value,
                e.ID return_value
                                
from S_EMP e, S_DEPT d
where e.DEPT_ID = d.ID and d.NAME = 'Sales'
order by 1

 

13.

Your LOV was created successfully. Now you can add it to the item on your page. Click the Edit Page icon.

 

14.

Select the Items icon to navigate to the items area under Page Rendering.

 

15.

Select the P5_SALES_REP_ID item link.

 

16.

Change the Label to Sales Representative and the Display As to Select List and select the LOV tab.

 

17.

Select SALES_REPRESENTATIVES from the list of Named LOVs and click Apply Changes.

 

18.

Click Run.

 

19.

Select a Sales Representative from the list and click Apply Changes.

 

20.

Your change was processed. Click the Home breadcrumb.

 

21 .

Select the Reports image.

 

22.

Select the Employees image.

 

23.

The interactive report with the list of Employees is displayed. Select a View icon next to one of the rows in the report.

 

24.

Notice that you can view the details but you can not make changes. Click the Home breadcrumb.

 

25.

You want to review the generated Master-Detail form for the S_ORD block. Select the S_ORD icon.

 

26.

The interactive report with the list of Orders is displayed. Select one of the edit icons in front of one of the rows to view the Master-Detail page.

 

27.

The Master-Detail page is displayed. The payment type has been generated as a radiogroup and the order filled as a checkbox. By pressing the less than and greater than arrows you can navigate from one order to the next and the items displayed will change accordingly. You want to make a few changes to the page. Click the Edit Page link in the developer toolbar.

 

28.

The first change you want to make is to create the validation that you reviewed in the Forms Conversion OBE migration project. You would like to create a validation that checks to make sure the ship date is later than the order date. Under Validations, click the Create Validation icon.

 

29.

Make sure Item Level Validation is selected and click Next.

 

30.

Select the P7_DATE_SHIPPED item and click Next.

 

31.

Select PL/SQL and click Next.

 

32.

Select PL/SQL Expression and click Next.

 

33.

Enter P7_DATE_SHIPPED less than P7_DATE_ORDERED for the Validation Name and click Next.

 

34.

Enter :P7_DATE_ORDERED <= :P7_DATE_SHIPPED in the Validation field and Ship date is before order date! in the Error field. Then click Next.

 

35.

Select SAVE (Apply Changes) for When Button Pressed and click Create.

 

36.

Your validation was created. You can also use the SALES_REPRESENTATIVE named LOV you created earlier on this page. Under Items, select P7_SALES_REP_ID.

 

37.

Select SALES_REPRESENTATIVES for Named LOV and select the Show All tab.

 

38.

Enter Sales Representative for the Label and select Select List for Display As and click Apply Changes.

 

39.

Click Run.

 

40.

Change the Date Shipped to one day less than the Date Ordered and click Apply Changes.

 

41.

Note that an error occurred due to the validation you created. Also notice the Sales Representative is a drop down list now. Click the Home link in the developers toolbar.

At this point, you can update the generated Application Express Application with your specific requirements or modify the Migration Project and generate a new Application Express Application.

In the next section, you review the conversion project and what changes take place after you create your application.

 

Back to Topic List

Examining Your Conversion Project After Generation

In this section, you examine your conversion project now that it has been converted. Perform the following steps:

1.

On the Workspace home page, under the Migrations region, click Application Migrations .

 

2.

Select your Conversion project from the list.

 

3.

From the Tasks Menu, select Generated Applications.

 

4.

Select your application from the list.

 

5.

The Application page is shown with a list of all the pages that were generated. Click the Home breadcrumb.

 

6.

On the Workspace home page, under the Migrations region, click Application Migrations .

 

7.

You can view the details about the Conversion Project as a whole. Click the edit icon in front of the conversion project.

 

8.

On the right side of the window, you can view the Completion Status of the Project.

 

9.

On the left side of the window, you can view details about the project. Notice the Associated Application field which you can change by clicking the List of Values icon in the Project Details region.

Now that you have implemented the WHEN-VALIDATE_RECORD trigger as a validation in your Application Express application, you can set the Complete field to Yes for that trigger. Click the Application Migrations breadcrumb.

 

10.

Select the number of triggers link.

 

11.

All the triggers are shown in this conversion project. You had already tagged the WHEN-VALIDATE-RECORD trigger with the tag VALIDATION. As a result, you can create a filter to show only the triggers with tags=VALIDATION. Select Filter from the Action pulldown.

 

12.

Select Tags from the list of Columns and enter VALIDATION in the Expression field. Then click OK.

 

13.

Notice that the WHEN-VALIDATE-RECORD trigger is shown. Select the Edit icon.

 

14.

Change Complete to Yes and click Apply Changes.

 

15.

Your trigger in the conversion project was updated. Click the Project breadcrumb.

 

16.

Notice that since you have a generated application, from the Project page, you can click the Run Application button to run the generated application.

 

17.

The Application menu appears.

 

Back to Topic List

Summary

In this tutorial, you learned how to:

 Create and upload files into a migration project
 Edit your migration project
 Review and analyze the objects in your project
 Add more files to your project
 Create and run your Application Express application
 Examine your conversion project after generation

Back to Topic List

Related Information

 You can find the Application Migration Guide on the Application Express Documentation Page on OTN
 Other Application Express information on OTN

Back to Topic List

 Place the cursor over this icon to hide all screenshots.

 

 

Left Curve
Popular Downloads
Right Curve
Untitled Document