Oracle APEX Cookbook Second Edition

Oracle APEX Cookbook Second Edition
Oracle APEX Cookbook Second Edition

Oracle APEX Cookbook Second Edition

After months of hard work, Marcel van der Plas and I have finally finished the Second Edition of our Oracle APEX Cookbook.
Two new chapters and many new recipes describe how to build even better APEX applications.
The first new chapter is on HTML5 and CSS3 and explains how to use new item types, templates and much more.
The other new chapter explains how to build applications for Mobile devices like Smartphones.

To ensure the quality of the book, many well-known names from the Application Express community have helped to review the book. People like Dimitri Gielis, Maarten van Luijtelaar and Surachart Opun reviewed the first edition. To this line-up the names of Learco Brizzi, Iloon Ellen-Wolff, Ronald Rood, Tony Jedlinski and Benjamin Kweku Intsiful have been added.

The book can be purchased on the website of Packt Publishing and many other webstores.

The author and his book

The author and his book

Passed APEX 3.2 Beta Certification

Oracle Application Express Developer Certified Expert Logo

Yesterday I returned from my summer vacation in the French town of Pierrefite-sur-Sauldre. Me and my family spend 2 wonderful weeks in the Alicourts resort.

This morning I opened my e-mail for the first time and found a nice little surprise from the Oracle Certification Program.
A while ago (in february) I participated in the Application Express 3.2 certification beta program. I took the exam in Utrecht and waited for the beta period to end.
The e-mail I received from Oracle and Pearson VUE contained a link to my Score Report. The verdict was: Passed!

So now I’m officially certified in “1Z1-450: Oracle Application Express 3.2: Developing Web Applications” or Oracle Application Express Developer Certified Expert for “short” :)

TNS_ADMIN for multiple Oracle Homes

Last week I tried to install Oracle Forms 11g. This install created a new Oracle_Home on my computer, which resulted in my database not starting up anymore.
The cause of this was, that my computer did not pick up the right tnsnames.ora file anymore, but instead it picked up the new tnsnames.ora that was created during my Forms installation.

To avoid having to have multiple copies of the same tnsnames.ora file on your computer, you can create a registry key in all Oracle_Homes. This will force your computer to pick up the same file for all your Oracle_Homes and thus allow you to have just a single copy of tnsnames.ora to manage.

To do this, start the Windows Registry editor by using Start -> Run -> Regedit and find your Oracle_Homes under HKEY_LOCAL_MACHINE -> SOFTWARE -> ORACLE and rightclick on your Oracle_Home entry. Choose New -> String Value and enter the required information similar to the following screenshot (directory on harddrive may differ on your computer).

TNS_ADMIN registry entry

The registry key is called TNS_ADMIN and it’s value is the location of your tnsnames.ora file on the harddrive.
But remember to only add this key to the Oracle_Homes that do not contain your central tnsnames.ora.

Oracle Netherlands 25 years

This year is the 25th anniversary of Oracle’s branch in The Netherlands. For this occasion Oracle has organised an event in Aalsmeer on november 19th.
My employer Whitehorses is a silver sponsor for this event. You can visit our stand for more information on the company and how we can help you solve your IT-problems.
For more information visit the event’s website.

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.

My Whitebooks

I work at the company Whitehorses. Each month we publish 2 so-called Whitebooks. These are articles written by us about things that are interesting for us and our customers.

In the past few years I’ve written some of these Whitebooks aswell. You can read them (in dutch) by following these links:

Kapow Mashup Server: content integratie met een knal
Java 1.5 New features
Oracle Forms JDAPI: bulk-wijzigingen vereenvoudigd
Kapow Mashup in de praktijk
Oracle 11g feature van de maand: Efficiëntere PLSQL code schrijven
RFID: risico’s en beveiligingsmaatregelen