Insider tips for Oracle Application Express
Very recently, Oracle has decided that free may be the way to go on many of their software packages. They have released a free SQL Development tool (SQL Developer), a free database (Oracle XE), free filesystems (OCFS, OCFS2), and a free fast application development environment (Application Express). It is the latter on which we will be focusing.
Oracle touts Application Express (called ApEx throughout this whitepaper) as a rapid application development framework, capable of making both simple and complex application for the web that accommodate all needs. Most users develop with the most basic of its abilities. This paper aims to show you the more advanced capabilities of ApEx, and how you can use it to develop complex and feature-rich environments.
Let’s Dive Right In!
This presentation will include a list of advanced tips and tricks, some including scripts, to get the most out of your ApEx applications.
These tips are geared towards allowing you to build a robust and large-scale application.
Change Control in Application Express
Change control is critical for all ApEx applications. Management must have a change control procedure that captures all ApEx components. ApEx has some built-in reports that provide basic information, but a formal change control procedure requires scripts to locate all changed components.
Home>Application Builder>Application xxx>Application Reports>Activity Reports
The ultimate goal of ApEx change control is to have a script that accepts the last production date and build a list of all schema components and ApEx chunks and exports them for migration into production.
Here is an ApEx script to query wwv_flow_steps, wwv_flows, and wwv_builder_audit_trail to display all components AFTER the last production migration date! This is not a complete change control solution because it does not consider inter-application dependencies, but it will reliably produce a list of ApEx components after a specific date:select t.audit_date, decode ( t.audit_action, 'I', 'Create', 'D', 'Delete', 'Change' ) audit_action, t.flow_user, t.flow_id, f.name application_name, t.page_id, s.name page_name, t.flow_table, t.flow_table_pkfrom wwv_flow_steps s, wwv_flows f, wwv_flow_builder_audit_trail twhere f.id = t.flow_idand s.flow_id = t.flow_idand s.id = t.page_idand t.audit_date > :migcheck_last_prod_migration_datetimeorder by t.audit_date desc;
Case Insensitive Searching in Application Express
In 10g release 2, we have the option of setting case insensitivity to function with the LIKE operator. Here we show how to take advantage of that powerful new feature in ApEx.
Setting the Oracle Parameters for case insensitive searching
We need to set NLS_COMP=LINGUISTIC and NLS_SORT=BINARY_CI in order to use 10gR2 case insensitivity. Since these are session modifiable, it is not as simple as setting them in the initialization parameters. We can set them in the initialization parameters but they then only affect the server and not the client side. The following PL/SQL was used for a search screen:
execute immediate 'alter session set NLS_COMP=LINGUISTIC';
execute immediate 'alter session set NLS_SORT=BINARY_CI';
Tuning for case insensitive queries in Application Express
Depending on the size of the table and type of queries, we may want to build linguistic indexes on the columns to allow case insensitive queries. This is the sample code for an index:create index nlsci1_gen_personon MY_PERSON (NLSSORT (PERSON_LAST_NAME, 'NLS_SORT=BINARY_CI') );
This is a great example of using case insensitive data inside Oracle and creating a case insensitive index to allow queries to me made in initcap, upper or lower, all without invalidating the index.
Using regexp_like with case insensitive searches
This example shows using both the 10g case-insensitive method as well as regular expression searching.SQL> select name from names; NAME-------------suzy smithSuzy SmithSUZY SMITH SQL> alter session set NLS_COMP=ANSI;SQL> alter session set NLS_SORT=BINARY_CI; SQL> select name from names where name = 'Suzy Smith' NAME-------------suzy smithSuzy SmithSUZY SMITH
So for our example of searching for 'Suzy%' and having an NLS_SORT for case sensitive searching (NLS_SORT=BINARY) we could issue the following types of SELECT statements and get the following results:SQL> select name from names where REGEXP_LIKE(name,'Suzy'); NAME-----------Suzy Smith SQL> select name from names where REGEXP_LIKE(name,'Suzy','i'); NAME------------suzy smithSuzy SmithSUZY SMITH
· As In-Line source code in the HTML Header section of the page attributes for an application page.
· As a Static File in Shared Components -> Static Files.
· As a file in a directory on the machine hosting the HTTP Server.
To exercise the steps in this section you should create a new application with a region including two page items: 1) a USERNAME page item of type Text Field; 2) a PASSWORD page item of type Password (don’t use the Password (submits when Enter pressed) option). Also create a button with the name SUBMIT in the region as Create a button in a region position.
The code here performs two functions. One is to set the focus of the page item and the other is to validate the username and password page items are not null before the page is submitted for processing.
The button you created is going to be used to execute the validateLogin function. If the validation is ok the function will then perform the SUBMIT for the page.
To upload the easy_java.js script into the script repository
1. Navigate to Shared Components for the Easy Samples application.
2. Click on the Static Files link.
3. Click the Create button.
4. Application: Select whichever application you are working with.
5. Filename: click the Browse… button and navigate to the Code Depot and select the easy_java.js file.
6. Click the Upload button.
Now you have added the easy_java.js script to the script repository and it is available for all application pages to take advantage of. Navigate back to the page attributes page for the Java Script page and edit the HTML Header text area to include the text as shown in the figure below.
Although the page attributes has a property named Cursor Focus that can be set to First item on page you may not want to set focus to the first item on the page. For this reason the setFocus( ) function may be the option you will want to use.
Using the Page HTML Body Attribute will modify the tag as shown here:
Note that you will likely need to change the name of the page item to match the name you have. Now when the page is rendered the focus will be placed on the Username text field.
Embedding Values Into a Calendar in Application Express
It's easy in ApEx to display dates in a calendar format. The ApEx calendar is designed to accept a list of calendar items with standard SQL commands.
Step 1: Write the SQL to return a list of DATE datatype columns.
We start with a SQL statement that will be used as input to the ApEx calendar. In this syntax example we grab a list of date columns for the current month:select flight_timefrom united_flight_occurrence_tablewhere to_char(scheduled_flight_arrival_time,'yyyy mon') = to_char(sysdate,'yyyy mon');
Step 2: Add the SQL to the ApEx calendar.
Next, we invoke an ApEx SQL type calendar as shown in the sample below. We start by defining a new region of the ApEx calendar type:
Figure 4. Select a Calendar Region
Next we have a choice of two types of ApEx calendar types, the ApEx Easy calendar and the ApEx SQL calendar:
Figure 5. Select SQL Calendar
In our example we choose the ApEx SQL Calendar option. We next name the calendar:
Figure 6. Name your Calendar
Now we add our SQL to pass the ApEx calendar with the parameters, a list of DATE datatypes, in this case, for only the current month:
Figure 7. Paste your Query
Then we click "create region" and we are all done! When we display the calendar our dates will appear in the calendar display.
Step 3: Test the ApEx Calendar
Let’s invoke the calendar to see the dates in the ApEx calendar display:
Figure 8. Calendar Complete!
Highlighting Within Your Calendar in ApEx
It's very useful to highlight calendar values within an ApEx calendar. You can create colored calendar visual displays in ApEx with a few adjustments to the calendar definition.
Highlighting the date text with bold red is done by wrapping the necessary columns with tags (or if you want to use styles). The Calendar Source SQL statement was modified as you will see below to get the result you'll see on the calendar.
Figure 9. A Calendar of a Different Color
Inside the ApEx calendar region definition, we change the SQL that populates the calendar to specify the display text color:
Figure 10. Adding Color to your Code
By adding the literal HTML tag to the SQL select statement the item will be displayed as red in the resulting ApEx calendar.
Note that you can do many more complicated things with this. By using case statements or the decode function, you can decide when a column value should be colored and when it should not!
Updateable Repeating Items in Application Express
All online applications must be able to model repeating groups of data, especially complex data entry forms where the end-user must enter variable occurrence screen items. Just a few examples include:
· Welfare application - Repeating list of dependents
· Order Form - Repeating list of items
· Security clearance application - repeating list of previous addresses
Languages have had this construct since the later 1950's, such as the Cobol syntax to allow repeating groups in online screens:
"DEPENDENT-LIST OCCURS BETWEEN 0 AND 40 TIMES DEPENDING ON DEPENDENT-COUNT."
So, how do we model repeating groups in ApEx? The ApEx reports already have superb built-in pagination functionality, But we need to model repeating sets on a screen that can be:
· Addressed by subscripts (e.g. year(i), skill(i))
· Tolerant of more entries being added
These is an example of an updateable repeating group in an ApEx screen. Note the button "Save and create more" that grows the repeating item list:
Figure 11. An updateable report with “add more” functionality.
This repeating list of update screen items is easily implemented as an ApEx report with a few simple buttons:
Figure 12. The Application Screen
We start my examining the "before header" process called "create collection". Here we see the wwv_flow_collection PL/SQL package invocation calling the create_or_truncate_collection stored procedure:
Figure 13. Creating the Collection
We see the named collection "MULTI_SKILLS" and a FOR loop calling the wwv_flow_collection packages add_member procedure, specifying the p_collection_name, and the three repeating updateable screen item values.
Next, let's look at the ApEx report that implements the screen with repeating update items:
Figure 14. The Report Region
In the ApEx reports region definition we see the region source using the ApEx_item package and the select_list_from_lov and select_list_from lov_x1 procedures. The region definition selects these values from the wwv_flow_collections table that we defined in the "before header" processing:
Figure 15. Report Source
Now let’s click the "Report:" link from the "page rendering" section of the report region screen.
Figure 16. The Report Region
Here we see the heading definitions for the repeating groups of data items and see their screen reference names (year, month, skill) that will be referenced via subscripts (e.g. skill(i)):
Figure 17. Report Heading Definitions
The code for updateable repeating groups in ApEx
Populating an updateable report is easy; here is a sample of some actual code. Note the in-line view to populate the values and the calls to the ApEx_item.text procedure:SELECT ApEx_item.checkbox(11,rownum) " ", x.addr_type, x.address, x.town, x.postal, x.countryfrom ( select ApEx_item.hidden(13,a.gen_adr_id)|| address_name addr_type, ApEx_item.text(14,a.adr_address_line1,25,240) address, ApEx_item.text(15,a.adr_town,20,240) town, ApEx_item.text(16,a.adr_postal_cd,9,9) postal from person_address g, master_address a where g.gen_adr_id = a.gen_adr_id and g.gen_person_id = :P2_PERSON_ID and g.expiry_date = active() ) xorder by 2
What this does is set up 5 arrays of one column each. You can’t create an array of more than one column. So each array will hold the values of the information you select from the table. In this case, we have the 5 arrays, designated by the 11, 13, 14, 15 and 16, which are referred to in ApEx as ApEx_application.G_F11, ApEx_appliation.G_F13 and so on. The country_name and isle_state_prov_name just display values.
Now the two things of importance in this query are the ApEx_item.checkbox(11,rownum) and the ApEx_item.hidden(13,a.gen_adr_id). The ApEx_item.checkbox(11,rownum) is used to set up the checkboxes, one for each row returned by the query with the rownum of the record. The ApEx_item.hidden(13,a.gen_adr_id) is used to set up a hidden item which will hold the primary key for the address record.
When ApEx does the processing, it recognizes which checkboxes are checked and returns the corresponding values in the array 11, because it is part of the main select. For example, if checkboxes 1, 3 and 5 are checked, array ApEx_application.G_F11 will have the rownums for the row corresponding to checkboxes 1, 3 and 5.
Updating an ApEx repeating screen display in ApEx
Here is the code to take the values from the updateable screen items and place them into an Oracle table:FOR i in 1..ApEx_application.G_F11.COUNTLOOP update gen_address set adr_address_line1 = ApEx_application.g_f14(ApEx_application.g_f11(i)), adr_town = ApEx_application.g_f15(ApEx_application.g_f11(i)), adr_postal_cd = ApEx_application.g_f16(ApEx_application.g_f11(i)) where gen_adr_id = ApEx_application.g_f13(ApEx_application.g_f11(i));END LOOP;
Here we have a loop that will run 3 times in our example. ApEx_application.G_F11.COUNT function returns 3, the count of values in the array. For each iteration, the ApEx_application.g_f11(i) will return the rownum of the checked record. Now we have the subscript for the arrays with the real values. This is a sample delete process for repeating update screen items.FOR i in 1..ApEx_APPLICATION.G_F11.COUNTLOOP delete gen_per_address where gen_adr_id = ApEx_application.g_f13(ApEx_application.g_f11(i));END LOOP;
We now have a ApEx updateable report where our end-user can enter repeating values directly into the ApEx screen, and we can reference each item by subscripts to extract the repeating items from the screen for storing into Oracle.
Conclusion on ApEx advanced features