About APEX – A developers blog

Situation: APEX 3.1.2 on a 9.2.0.6.0 database linking to Siebel.

When I was building a report on 2 tables, one local and one remote, I noticed a very big performance issue. I thought I had solved this by adding a DRIVING_SITE hint, but this wasn’t enough.

The query looked something like this:

select /*+ DRIVING_SITE(e) */
e.ename
, d.dname
from emp e
, dept@remote d
where d.id = e.dept_id
and d.loc = :P2_DEPT_LOC

In the page, a user can select a Department Location and press a button. The report data is then generated based on this selected location.

To debug this, I started investigating trace files on the remote database. What I immediately noticed was, that the Substitution Variable :P2_DEPT_LOC wasn’t replaced by it’s value that was selected in the APEX page. So that was the problem.

To solve this, I simply had to replace the way the Substitution Variable was called. The query was changed to:


select /*+ DRIVING_SITE(e) */
e.ename
, d.dname
from emp e
, dept@remote d
where d.id = e.dept_id
and d.loc = '&P2_DEPT_LOC.'


And everything was okay. The time it took to load the page changed from 45 minutes to 0.05 seconds.

But be aware! This kind of solutions opens the door for SQL Injection, so make sure that you handle the input before parsing the query.

It has now been over a month since the Oracle APEX 4.0 Cookbook has been published.
The first sales information and reviews are in and it is looking great.

It has been a real fun few weeks with a lot of congratulations and positive feedback.
On January 12th I had a meeting at Oracle Netherlands headquarters in De Meern to present the first signed copy of the book to Iloon Ellen-Wolff, the Global Technical Lead for Application Express for the Oracle Corporation.

Handing over the first signed copy

Also the first reviews for the book by fellow APEX developers started rolling in. Like these from Rinie Romme and Christian Rokitta. But also a great reference from Joel Kallman, the Director of Software Development at Oracle.

All in all things are still looking great for the book and I’m curious where it will lead me in the future. At least I’ve been noticed by the community now :)

For the past months I have been working together with Marcel van der Plas on a book on APEX 4.0. The publisher Packt has just released the website, so it can now be pre-ordered.

Find the link here: Oracle APEX 4.0 Cookbook

The book offers more than 70 recipes that cover almost all topics of Application Express 4.0.
We are currently finishing up and expect that the book will come out in January.

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” :)

Last week a new APEX book was published. It was written by Arie Geller and Matthew Lyon. It’s on the current version of APEX and covers basic and advanced features of Application Express 3.2.

I will post a review somewhere in the coming weeks, but since the publisher is the same that will be bringing out my own APEX 4.0 book later this year, I wanted to let you know that this is out there. You can find more information at the website of
Packt Publishing

I did another Whitebook (in dutch). This time the subject is New Features in the APEX 4.0 Early Adopters release.
You can find it at the Whitehorses website.

By the way; besides this personal blog I also publish articles on the blog at the Whitehorses site together with the other Oracle, Java and SOA specialists of the company. These blogs are in english. A direct link to the list of my blogs is in my profile.

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.

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

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