New Whitebook and blog

For my employer Whitehorses I have written a new Whitebook on Application Translations in APEX. This Whitebook was written together with colleague Ome-B, the owner of www.Ome-B.nl and author of the book Oracle Application Express Forms Converter.

Following up on this Whitebook I have posted a blog at blog.whitehorses.nl, to explain how these translations work in the early adopters release of APEX 4.0.

Configure APEX in Oracle 11g

Application Express comes packaged with the OracleXE and Oracle11g database versions. In XE you can get started with APEX right away. In 11g the embedded PL/SQL gateway has to be configured first. These steps take you through the process of running the configuration script apxconf.sql.
Running this script enables you to configure the port for the Oracle XML DB HTTP server and to specify a password for the Oracle Application Express ADMIN account. Then you unlock the ANONYMOUS account.

Perform the following steps:

1. Open a terminal window and enter the following commands:

cd $ORACLE_HOME/apex
sqlplus sys/ as sysdba
@apxconf

2. Enter an administrator password for the Application Express Administrator account and press Enter.

3. Enter 8080 for the port for the XDB HTTP server and press Enter. This is also the default port. Change this number when another application (eg. Tomcat) is running on the same port. Keep in mind that ports below 1024 are not advisable when running Linux/Unix.
(The embedded PL/SQL gateway has now been configured.)

4. Unlock the anonymous user. From your terminal window, enter the following command:

ALTER USER ANONYMOUS ACCOUNT UNLOCK;

And now you’re all set to start developing APEX applications in 11g.

When you later want to change the port on which APEX is configured, you can run the following command:
dbms_xdb.sethttpport('9090');
If you want to check what the current port is, use the following query:
select dbms_xdb.gethttpport from dual;

For more information, visit the Oracle site at www.oracle.com

Using audit columns with APEX

It’s good practice to use audit columns in Oracle to see which user created or modified certain records. Application Express is not different.

Filling audit columns is usually done using Before Insert and Before Update triggers on the related tables. These columns usually are Created_Date, Created_User, Modified_Date and Modified_User. Both date columns are filled with Sysdate, but the user columns are a bit different.

In most environments you can suffice with using the database user by setting the column like this:

:NEW.Created_User := USER;

In Application Express applications that’s not enough when you have an authentication scheme based on a user table. In that case you would like to see the application user that inserted or updated the record in you audit.
A simple and smart way to do this is by using the v() construction in your trigger like this:

:NEW.Created_User := nvl(v('APP_USER'),USER);

When you use this kind of code in your triggers, the audit user is filled with the user that was logged into the application. But when the record is altered directly on the database, you will see the database user.

Application Express Listener available

Oracle has put the Application Express Listener up for download. It’s an early adopters release, so it’s not supported, but I think it’s still worth giving a try.

The download is available at: http://www.oracle.com/technology/products/database/application_express/html/apex_listener_download.html

I think it’s a very significant change in Apex. This allows developers to use their existing web server (like Tomcat or Weblogic), instead of Oracle HTTP Server/mod_plsql or the XDB HTTP protocol server/embedded PL/SQL gateway.

Read the Installation Guide for more information.

Javascript confirmation popup in APEX

In my current project I’ve been trying to get a nice popup message to show when a user presses a button.
This can be done in several ways, of which most are cumbersome or hard to understand.

The easiest and (in my opinion) prettiest way is using a javascript popup. When Ok is pressed, the button performs it’s normal function. When Cancel is pressed, the focus returns to the page without submitting.
The only thing needed is a line of code on the URL target of the submit button.

If you want the button to submit when Ok in the popup is pressed, your code will look like this:

javascript:{if (confirm('Confirmation Message')) doSubmit('<button name>');}

If you want the button to redirect to a different page using a URL when Ok is pressed, you can use this code instead:

javascript:{if (confirm('Confirmation Message')) redirect('f?p=&APP_ID.:&APP_PAGE_ID.:&APP_SESSION.');}

Using dynamically generated checkboxes in Apex

In one of my projects, I’m building an application in Apex. This application depends entirely on webservices for its data. Which meant that I had to be smart in my design.
The project revolves around a system in which customers can create a survey. The survey is then presented to individuals using an Apex front-end. This means that all information on the front-end is shown dynamically.
One of the question types that is used, is presented using checkboxes. This proved to be quite a challenge in Application Express.
First of all I needed to understand the inner workings of Apex’s array collections. Normally an item in a form is put into a desired array using code like apex_item.hidden(1,value), which puts a value into array 1. So what I did in my project was to put all values in the form into an array and later select them by using a loop:

for i in 1 .. apex_application.g_F01.count
loop
insert into answer_table (question, answer)
values (APEX_APPLICATION.G_F01(i)
,APEX_APPLICATION.G_F02(i)
);
end loop;

But this is not the way to go with checkboxes, because a webbrowser only sends the checkboxes that are actually checked. This means that when you have a question with 5 possible answers and the user checks 2 of them, you get an array with 2 values per question. But because there is only one question, the arrays for question and answer go out of sync.
After browsing through the Oracle forums, I started to think of a solution to my specific problem.
What I eventually ended up with, was a specific array #40, just for questions with a checkbox. This array holds the question_id concatenated with the checkbox value and a letter X inbetween (because both the question_id and checkbox value are numbers, I can use any non-numeric character). This same array is filled with a hardcoded value for all other items, to distinguish them.
A simple textitem would look something like this:

htp.p(apex_item.hidden(1,question_id));
htp.p(apex_item.text(2,question_content));
htp.p(apex_item.hidden(3,question_type));
htp.p(apex_item.hidden(40,'XXTESTVALUEXX'));

And a checkbox item like this:

htp.p(apex_item.hidden(1,question_id));
htp.p(apex_item.hidden(2,null));
htp.p(apex_item.hidden(3,question_type));
htp.p(apex_item.checkbox(40,question_id||'X'||choice_id,p_checked_values => l_select_list,p_checked_values_delimitor => ';'));

Now when I need to pick up these items in my page, I first form a loop like the one in the beginning of this post with an extra IF to check if APEX_APPLICATION.G_F03(i) is not ‘CHECKBOX’.
After that I go through an extra loop which gets all items in apex_application.g_F40.count and where this statement is true APEX_APPLICATION.G_F40(i) <> 'XXTESTVALUEXX'.
With a little help from instr, substr and replace I can then pull out the question_id and selected checkbox value from array #40.