Using the advanced features of ApEx

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.

This presentation will show little-known techniques for performing complex screen interfaces and leveraging JavaScript and special techniques for large-scale systems development.  This includes embedding JavaScript, page content, and other advanced functions.

There are even functions beyond the scope of this presentation that provide more feature-rich functionality, such as using AJAX (Asynchronous JavaScript and XML) in order to eliminate page refreshes.  You can check out another of my presentations, ?Putting the Express Back Into Oracle Application Express with AJAX,? for more details.

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:

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:

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.

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:

Managing JavaScript in Application Express

Although JavaScript can be embedded almost anywhere within the HTML source code it is most commonly embedded between the <head> and </head> tags.  In ApEx this area is identified in each application page in the HTML Header region of the Page Attributes page.

Embedding JavaScript "See JavaScript"  source code in ApEx can be accomplished in any of 3 ways:

?        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.

Using a Button to call JavaScript in Application Express

Now you have setup the code in your application page so that other page items can make calls to the functions defined in the JavaScript.  Let's add the call to the JavaScript code to the Submit button we created earlier.

While you may be used to using the default submission settings for buttons, in this section we are going to override the default submit behavior of a button and cause it to call our JavaScript.  Editing the button attributes for the Submit button and setting the target URL to the code shown here accomplish this.

Figure 1.  Using a button to point to a JavaScript function.

Even though you are setting the target to URL you are not entering a traditional looking URL.  Instead you are entering a call to the JavaScript.

Using a JavaScript Code Repository  in Application Express

After you have developed enough JavaScript functions as described above you will want to put them into a script so you can make it reusable on several web pages.  If you already have a JavaScript file you have created previously, or downloaded reusable code from the internet, you can follow the same steps mentioned here to add the file to the script repository.

First, create the JavaScript file named easy_java.js using the code in the previous tip.  However, when converting the inline code from the HTML Header section you will need to remove the <script> and </script> tags.  These tags are necessary in the HTML Header section, but are not permitted when the code is in a JavaScript file. 

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.

Referencing the functions, in the uploaded JavaScript file, on a single ApEx page is done by adding the following code to the HTML Header section of the Page attributes page.

Figure 2.  The HTML Header with an external JavaScript library reference.

Run a JavaScript Function on Page Load in ApEx

One handy JavaScript function is setFocus.  This function accepts a parameter of the page item name to set focus to.  The place you will call this code from is on Page Attributes page in the On Load region.  Navigate to the page attributes page for the Java Script application page and enter the text shown in figure 3 below.  Then apply changes.

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.

Figure 3.  Calling JavaScript on Page Load

Using the Page HTML Body Attribute will modify the <body> tag as shown here:

<body onLoad="javascript:setFocus('P4_USERNAME');">

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:

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 <font> tags (or <span> 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 <font color="red"> 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:

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:

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.

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

There are many features that ApEx has available, though they require some digging and customization.  This presentation showed how using JavaScript and/or PL/SQL can break loose of the standard ApEx application to allow feature-rich, robust applications that provide the same functionality as other application development platforms.

About the Author

Steve Karam is a Sr. Consultant for Burleson Oracle Consulting and Training, and is responsible for troubleshooting dozens of high profile databases including RAC clusters.  He is also an accomplished instructor, and teaches dozens of courses for Burleson Consulting.

Mr. Karam has the honor of being both the second Oracle 10g Certified Master in the world and an Oracle ACE.  He enjoys helping others in the Oracle Community through writing, Q&A, and through meetings and events as the President of the Hampton Roads Oracle User's Group.   Mr. Karam is also an avid supporter of the Oracle Academy, Oracle's high school education initiative, as he began his Oracle career while in high school. Steve also enjoys blogging on his website, http://www.oraclealchemist.com, when time permits.