Oracle Application Express Forms Converter — Save 50%
Convert your Oracle Forms applications to Oracle APEX successfully using this book and eBook
In this article by Douwe Pieter Van Den Bos, we will get ready for our Forms conversion and generation. In this part of our conversion project, we will investigate, analyze, and adjust some of the most important parts of our application. This means that we will set everything up for the generation of the application. We will discuss the following parts of the conversion project in this article:
- Investigating the components that will be generated
- Getting to know the database blocks in our Forms files
- Looking deeper into the block items inside our blocks and editing them
- Enhancing the queries on which our blocks are based
- Analyzing the triggers we have in the Forms XML files
- Massively changing the completeness and applicability of triggers or items
- Customizing the query that the blocks are based on in order to complete our generation
- Understanding the way our pages will be generated in APEX
- Editing the titles of our blocks and items
- Analyzing our business logic (probably the most important part)
After reading this article, we will understand our project a lot better. Also, to a certain level, we will be able control the way our application will be generated. Generation is often performed more than once as you refine the definitions and settings between iterations.
In this article we will learn a lot of ways to edit the project in order to generate optimally. But we must understand that we will not cover all the exceptions in the generation process. If we want to do a real Forms to APEX conversion project, it will be very wise to carefully read the help texts in the Migration Documentation provided by Oracle in every APEX instance—especially the appendix called Oracle Forms Generation Capabilities and Workarounds, which will help you to understand the choices that can be made in the generation process. The information in these migration help texts tells us how the different components in Oracle Forms will be converted in APEX and how to implement business logic in the APEX application. For example, when we take a look at the Block to Page Region Mappings, we learn how APEX converts certain blocks to APEX regions during conversion.
When we take a look at our conversion project, we must understand what will be generated. In case of generation, the most important parts are the blocks on our Forms modules. These are, quite literally, the building blocks our pages in APEX will be based upon. Of course, we have our program units, triggers, and much more; but the pages that are defined in the APEX application (which we put in production after the project is finished) will be based on Blocks, Reports, and Menus. This is why we need to adjust them before we generate anything. This might seem like a small part of the project as we look at the count of all the components in our project page, but that doesn't make it less important.
We can't adjust reports as they are defined by the query that they are built upon, but we can alter the blocks. That's why we focus on those components first.
The building blocks of our APEX pages are the blocks and, of course, the reports. The blocks we can generate in our project are the ones that are based on database block. Non-database blocks such as those that hold menus and buttons are not generated by default, as they will be generated as blank pages. In the block overview page, we get the basic information about the blocks in our project. The way the blocks will be generated is determined by APEX based on the contents, the number of items on the block, and, most importantly, the number of records displayed. For further details on the generation rules, refer to the Migration Guide—Appendix A: Forms Generation Capabilities and Workarounds.
In the Blocks overview page in our conversion project, we notice that not all the blocks are included. In other words, they aren't checked to be included in the project. This is because they are not oriented from a database block. To include or exclude a block during generation, we need to check or uncheck the specific block. Don't confuse this with the applicability of a block. We also might notice that some of the blocks are already set to complete. In our example we see that the S_CUSTOMER1 and S_CUSTOMER blocks are set to complete. If we take a look inside these components and check the annotations, they are indeed set to complete. There's also a note set for us. As we see in the following screenshot, it states Incorporating Enhanced Query:
The Enhanced Query is something that we will use later in this article. But beware of the statement that a component is Complete as we will see that we might want to alter the query on which the customer's block is based.
If we look at a block that is not yet set to complete in the overview page (such as the Orders block) and we look at the Application Express Page Query region in the details screen, we see that only Original Query is present. This is the query that is in the original Forms XML file we uploaded earlier.
Although we have the Original Query present in our page, we can also alter it and customize the query on which this block is based. But this will be done later in the article. In this way, we have a better control over the way we will generate our application. We can't alter this query as it is to be implemented as a Master-Detail Form.
Each block contains a number of items. These items define the fields in our application and are derived from our Forms XML files. In the block details pages, we can find the details of the items on the particular block as well. Here we can see the most basic information about the items, namely their Type, Prompt, Column Name, and the Triggers on that particular item. We can also see the Name of the item if it is a Database Item and if the item is complete or not, and whether or not it is Applicable. When a block is set to complete, it is assumed that we have all the information required about the items, as we see in the example shown here:
But there are also cases where we don't get all the information about the items we want. In our case, we might want to customize the query the block is based on or define the items further. We will cover this later in the article.
In the above screenshot we notice that for all the items the Column Name is not known. This is an indication that the items will not be generated properly and we need to take a further look into the query and, maybe, some of the triggers.
When we want to alter the completeness and applicability of the items in our block, there's a great functionality available on the upper-right of the Blocks Details page. In the Block Tasks section, we find a link that states: Set All Block Items Completeness and Applicability. This function is used to make bulk changes in the items in the block we are in. It can be useful to change the completeness of all items when we are not sure what more needs to be done.
To set the completeness or the applicability with a bulk change on all the items, we click on the link in the Block Tasks region and this takes us to the following screen:
In the Set Block Item & Trigger Status page we can select the Attribute (Items, Block Triggers, or Item Triggers), the Set Tracking Attribute (Complete or Applicable), and the Set Value (Yes or No). To make changes, set the correct attribute, tracking attribute, and value, and then click on Apply Changes.
Original versus Enhanced Query
As mentioned earlier, we can encounter both Original and Enhanced Queries in the blocks of our Forms. The Original Query is taken from the XML file directly as it is stated in the source of the block we are looking at. So where does the Enhanced Query originate from? This is one of the automatically generated parts of the Forms Conversion tool in APEX. If a block contains a POST QUERY trigger, the Forms Conversion tool generates an Enhanced Query for us.
In the following screenshot, we see both the Enhanced Query and the Original Query in the S_CUSTOMER block. We can clearly notice the additional lines at the bottom of the Enhanced Query.
The query in the Enhanced Query section still looks a lot like the one in the Original Query section, but is slightly altered. The code is generated automatically by taking the code from both the Original Query and POST QUERY triggers on this block. Please note that the query is automatically generated by APEX by adding a WHERE clause to the SQL query. This means that we will still need to check it and, probably, optimize it to work properly.
The following screenshot shows us the POST QUERY trigger. Notice that it's set to both applicable and complete. This is because the code is now embedded in the enhanced query and so the trigger is taken care of for our project.
Besides items, even blocks contain triggers. These define the actions in our blocks and are, therefore, equally important. Most of the triggers are very Forms-specific, but it's nice to be the judge of that ourselves.
In the Orders Block, we have the Block Triggers region that contains the triggers in our orders block. The region tells us the name, applicability, and completeness. It gives us a snippet of the code inside the trigger and tells us the level it is set to (ITEM or BLOCK).
A lot of the triggers in our project need to be implemented post-generation, which will be discussed later in this article. But as mentioned above, there is one trigger that we need in the pre-generation stage of our project. This is the POST-QUERY trigger.
In this example, the applicability in the orders block is set to No. This is also the reason why we have no Enhanced Query to choose from in this block. The reasons behind setting the trigger to not applicable can be many, and you can learn more about the reasons if you read the migration help texts carefully.
We probably want to change the applicability of the trigger ourselves because the POST-QUERY trigger contains some necessary information on how we need to define our block. If we click on the edit link (the pencil icon) for the POST-QUERY trigger, we can alter the applicability.
Set the value for Applicable to Yes and click on Apply Changes. This will take us back to the Block Details screen. In the Triggers region, we can see that the applicability of the POST QUERY trigger is now set to Yes.
Now if we scroll up to the Application Express Page Query region, we can also see that the Enhanced Query is now in place. As shown in the following screenshot, we can see that we automatically generated an extended version of the Original Query, embedding the logic in the Post Query trigger. For the developers among us, we can see that the query produced by the conversion tool in APEX doesn't make the query very optimal. We can rewrite the query in the Custom Query section, which we will describe later in this article.
We are able to set the values for our triggers in the same way we used to set the applicability and completeness of the items in our blocks.
In the upper-right corner of our Block Details screen, we find the Block Tasks region. Here we find the link to the tasks for items as well as triggers.
Click on the Set All Block Triggers Completeness and Applicability to navigate to the screen where we can set the values. In the Attribute section, we can choose from both the block level triggers as well as the item level triggers. We can't adjust them all at once, so we may need to adjust them twice.
eBook Price: $23.99
Book Price: $39.99
We already learned how we can incorporate an Enhanced Query as the base for the blocks in our project, but sometimes we would want to edit or adjust the query ourselves. This is why we have the possibility to create a custom query in our blocks. If the enhanced or original query isn't sufficient, we can enter our own custom query.
It would be strange to build this custom query from scratch, as we can use either the original or, sometimes, the enhanced query as a basis. We are able to copy the code in the Original or Enhanced query sections to the custom query section. This is done with the copy icon underneath the Original and Enhanced Query boxes. In the following screenshot, we see this button in the S_CUSTOMER block.
In this example, let's click on the copy icon underneath the Enhanced Query to copy this code into the Custom Query section. As we can see in the next screenshot, the query is copied into the Custom Query field, which we can edit ourselves:
We are not quite there yet as we have only the enhanced query in our custom query section. We want our own query in the S_CUSTOMER block because we don't have all the data selected from the database yet. When we take a look at the block items in S_CUSTOMER, we see that the Sales Rep Name is in the items, but we don't get it entirely from our query. Both the original and the enhanced query don't select the sales rep's first name from any source in the database.
So we need to make some changes to the code we copied to the Custom Query section. In this case, the sales rep's first name comes from the S_EMP table, which is related to the S_CUSTOMER table by the ID of the Sales Rep. In this way we can edit the query, so we select the sales rep's first name from the S_EMP table and put it together with the Rep's last name in order to get his or her complete name. This query is shown in the following screenshot:
When we're done editing the query the way we want, we need to tell the Forms Conversion tool to use the Custom Query when generating the APEX application. This is done in the Use Query field. Here we can select the query we want to use during generation. Once you have selected the Custom Query, click on Apply Changes to save.
In the migration help texts, we find a lot of information about how and, specifically, why blocks are generated in APEX. In this part of the article we will discuss just a few of them. All blocks that are included in the project will be generated and we will see that it's not always the case that one block is one page in APEX.
For example, let's take a look at the Orders block. In the upper-right corner of our details page, we find a field that's called Block Status. It gives us information about the block in question and its contents. Here we see that the Orders Block contains 1 Block, 9 Items, 22 Triggers, that is, a total of 32 Components of which 21 have been Completed. This teaches us that we have a completion of over 65%.
Also very interesting for us at this point is the Convert As information in the Block Status field. The Orders block will be generated in APEX as a Report and a Form page. This means that there will be two pages for one block. The main page shall be the report. When we click on the edit link in the report, when it's in APEX, we will be taken to the Form to edit the specific row.
Now let's take a look at a different type of block. This time we are going to investigate the Inventories block. When we look at the Block Status field, we see that the block has not been completed at all and that it is generated in APEX as a Tabular Form:
How is this possible and why does the Convert As information say it will be generated as a Tabular Form?
When we take a look at the Block Details section of this block, we see that we are able to update, insert, and delete records using this block. This means that we have a Form on our hands. We also see in the Records Display Count that we have four records shown in the block. This is the reason why the Forms Converter decides that we need a Tabular Form. Makes sense, right?
Before we go and generate our application in APEX, we want to set everything up properly. In our conversion project, we have a lot of nice editing possibilities in place. It's a lot easier to edit the titles and prompts in this phase of our project than doing it afterwards. As we can see in the block overview page in our conversion project, a lot of our blocks have nice, clean titles—but not all of them. The S_CUSTOMER1 and S_CUSTOMER titles from the customers_fmb.xml state are not nice titles to have on our page in the APEX application that we are about to generate.
In the blocks overview page, we can immediately edit the Title of the blocks. Simply clicking on Title for the block in question does this.
In our example, we're going to change the title for the S_CUSTOMER block to Customers:
Click on Apply Changes to confirm the changes we made in the Title. In this way, you can alter the titles of all the blocks according to your application. We can also change the titles of all the items on our blocks. In the Blocks Overview page, click on the Item Count link. This takes us to the items in this block. In our example, we take a further look at the items in the S_CUSTOMER1 block:
This shows us that the titles of a lot of items are not filled in correctly or are not present at all. You can change them all here by clicking on Apply Changes to confirm the changes we have made.
Analyzing business logic
The most important task we need to do at this stage (pre-generation) of our conversion project is analyzing what we want to do with all the business logic that's in our application. It's time to go into the quality of the business logic in our application. Implementing the business logic is done post-generation, except for some Post-Query triggers. At this stage, it's very important to know in what way we need to implement it before we start generating.
One of the features of Oracle Forms is that we have clean alerts that we can set to an action. In this way, the users are given information about the actions they are about to do. It would be nice for the users to know the functionality before they start generating. For example, click on the number of alerts link for the customers_fmb.xml file. This takes us to the overview page of all the alerts in the Customers Forms application.
Program units can be very Forms-specific. This is certainly the case in our example. One of the most common ways is to implement the program units as application or page processes, but, by and large, this is not the best place to do this. It's commonly preferred to implement program units as a PL/SQL package in the database instead of an APEX application or page process. There's information in the Migration Help texts on how we can implement the program units in our application after generation.
It's certainly not always the case that we want to implement program units as a page or application process. Let's take a further look in one example. The REFRESH_TREE_SALESREP program unit in the customers_fmb.xml file is quite interesting. If we take a look at the code that's in the Program Unit Details page, we can understand why this one might be different.
PL/SQL libraries are basically stored procedures implemented in an Oracle Forms Application. To implement this functionality in the APEX application, we are about to generate a few options. Based on the functionality that's in the PL/SQL Library, we need to analyze what we need to do post generation. Roughly, the possibilities are to create a stored procedure or function in the database and maybe some page or application processes to call them. But certainly, there are more ways to implement this. For example, let's take a look at the example in our Forms Conversion project. We have already uploaded the wizard.pld file to the project. Click on the PL/SQL Library link in the project page to go to the PL/SQL Library Details page.
This package has some components that are very Forms-specific. But if we take a further look at the actual code in the package, specifically the Wizard_Show procedure, we will notice that there's some functionality in it that we want in our APEX application.
The code for the Wizard Show procedure looks a lot like the "conditional" settings we have on the buttons in APEX. We might want to implement this as conditional buttons in APEX post generation. To be even more specific, we might want to consider doing this on page zero in our application, or the template the application is built on, because this functionality can be used on more than one page. Therefore, it is a general function.
The bulk of the business logic in any Forms application comes from triggers. Most of them are Forms-specific and we might not use them in any APEX application because of the basic differences in the way the application is used. Our example isn't any different from this. When we take a look into the Triggers section of our conversion project, we see that we have a total number of 68 triggers. This means that we have a lot of functionality that we need to look over to be sure we embed the necessary functionality in our APEX application. If we look at the names of the triggers, we learn that they are very self-explanatory.
In this example we take a further look at a WHEN_BUTTON_PRESSED trigger on the Orders Forms application. Select the appropriate Trigger Name in the Triggers overview page and take a look at the item-level WHEN-BUTTON-PRESSED trigger on the Orders Forms application.
Here we see that the trigger points to another block, the Inventories block, on the Orders Forms application. Do we need to implement a page branch after generation? We would probably define a button on the Orders page called StockButton, which branches to the Inventories page. Therefore, we should update the annotations accordingly.
These are just examples. Every trigger needs to be evaluated unless the applicable is set to No. Look into the help texts about Forms conversion in APEX for more information. The appendix of the Oracle Forms Generation Capabilities and Workarounds can especially be extremely helpful in order to understand what we need to do with triggers and other business logic in the application we are going to generate that we will discuss post generation.
In this article we learned quite a lot about the steps we need to take before we start the generation of our APEX application. The steps described in this article may look quite basic, and in a way they are, but they are very important in order to create a working and functional application after generation.
This doesn't mean we can relax because we saw that most steps have to be done post generation. The business logic has to be implemented in the APEX application to replicate the necessary functionality in the new APEX application.
We did the following steps to prepare ourselves for generation:
- We created the correct query a Block is based on by using the information in the original or enhanced query. With this we created a custom query that collects the necessary data from the database and fills all the items in our block with the correct information.
- We learned how to use bulk changes in the triggers and items to track the progress after generation.
- Editing the blocks and items, especially the titles they work with, was another step we took to make sure the generation will be a success.
- We've taken a look into the business logic in our Forms application and saw some examples on how to implement them after the generation.
- We have learned that a lot of information is in the Application Migration help texts in APEX that we can use to determine why and how some elements will be generated.
eBook Price: $23.99
Book Price: $39.99
About the Author :
Douwe Pieter van den Bos started working as an Oracle Developer using Oracle Designer and Oracle Forms. Soon he discovered the wondrous world of Oracle Application Express and was one of the first people in the Netherlands to be using this tool in real live applications. His first encounters with the development of APEX applications and, later on, his thoughts on web development and project management were written down on his own personal website, http://ome-b.nl. This web site became the only Dutch APEX related website and a knowledge base on everything APEX.