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.

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.

Smart queries: find duplicate rows

On every project I’ve worked for the past few years, there came a question to find all duplicate rows in a table. And every time I mix up some of the code. So now it’s time to post the right code on my blog, so I can always find it back :-)


SELECT employee,
COUNT(employee) AS Occurrences
FROM emp
GROUP BY employee
HAVING ( COUNT(employee) > 1 )

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.

New Whitebook published

Last week my latest Whitebook article was published at the Whitehorses company website.
The article is on the RFID implementation at Centraal Boekhuis. That is the biggest partner in logistics for books in the Netherlands and Benelux region. There I worked on the first big RFID implementation for a retail supply-chain in the Netherlands.

Find the article here.

Oracle buys Sun Microsystems

Oracle buys Sun

Oracle buys Sun

It has finally happened. Sun has been taken over. Not by IBM, but by Oracle!

This move implies that Larry Ellison has big plans in his competetive struggle with Microsoft. Because with Sun come a lot of nice bonusses. The operating system of Solaris, SPARC hardware platform, VirtualBox, the Java programming language and the MySQL database just to name a few. And not to forget: OpenOffice.
Oracle can now offer services on every level from hardware to software. From small companies to large multinationals. Maybe in the future we will run an Oracle Operating System and write our documents in Oracle Open Office on our Oracle Desktop Machine…

Time will tell…

More info: http://www.sun.com/aboutsun/media/presskits/2009-0420/index.jsp