Oracle DB 12c, APEX 5.0 and APEX tech sessions at OGH APEX World

April 9th 2013, APEX World took place, one of the biggest APEX only events in the world, organized by the Dutch Oracle user group (OGH). iAdvise was gold partner of this year’s edition. Below is a view on this event from our perspective.

iAdvise stand

iAdvise booth

The event took flight with the keynote by David Peake, product manager from Oracle. He spoke about Oracle DB 12c, what it is and what’s in there for APEX developers, and some words about what to expect from APEX 5.0. Again, we had to conclude that great things are coming our way in the months to come:

  • pluggable databases,
  • new column types,
  • data redaction,
  • new IDE-like view for the page builder,
  • multiple interactive reports on 1 page,
  • multi-row edit region type,
  • enhanced builder security
  • and some more great stuff…

But as we are used to with Oracle, it’s all subject to change and might not make it into next release :-)
More in-depth details and another view on this presentation can be found on Christian Rokitta’s blog.

The only two things David was clear about were:

  • APEX 5.0 will focus on improved usability and quality rather than on new features;
  • there will be an APEX 4.2.2 release before 5.0 will become available.

Below are our highlights from the presentations we followed.

Transfer Solutions presented their “portal” solution to combine both Forms and APEX applications with one custom single sign-on. We saw some interesting approaches for common issues and found confirmation for our approach in comparable situations.

Rob van Wijk from Ciber presented his white paper “Professional software development using APEX”. Together with his colleagues, he worked out a way to version database schemes and APEX applications in Subversion when all developers are working in their own isolated environment. On top of this, he used Hudson to automate the versioning update-commit process. A must read paper if you consider versioning within your APEX environment!

Regarding responsive web design (RWD), there were two presentations of interest:

Dimitri Gielis introduced how RWD can be accomplished in APEX by using the new built-in grid system and spoke about APEX theme 25 (fully responsive and new in APEX 4.2) but also Twitter bootstrap, which can perfectly be integrated in APEX.

Later on, it was up to CB Logistics, together with iAdvise colleague Stijn Van Raes, to present how they used RWD in a recent project.

Peter Raganitsch and Matt Nolan from FOEX came all the way to Zeist to state that APEX truly is the best successor of Oracle Forms…but not out-of-the-box. In a few demos, in which they combined declarative APEX functionality together with their plug-in framework, they showed how APEX can become a perfect fit for everyone who wants to migrate good old Forms without losing much functionality, usability and maintainability. A bold statement but definitely something our APEX developers will look into.

Roel Hartman showed us what mobile web development with APEX theme 50 and jQuery mobile is all about. He walked through the new item types, lists and buttons and demonstrated how you can easily make your application look better with standard APEX functionality.

Our iAdvise colleague, Karen Van Hellemont, presented with Natasha Van Hoof from the Flemish Government how documents can be generated from the database. Throughout several years, iAdvise gained experience in generating documents with PL/SQL and developed a metadata driven solution with a simple API to re-use it in other projects. The Flemish Government is our first customer to use the complete version of this solution and therefore gave a customer testimonial on their experience with the tool.

The price for most-entertaining presentation definitely goes to John Scott (APEX Evangelists) when talking about HTML5 websockets! The concept of websockets took about 10 minutes. The remaining time was used for demos about what you can do with this. We never heard so many sounds of astonishment in 30 minutes!

For a Belgian-Dutch Oracle consultancy company, this is one of the most valuable events next to Oracle Open World and this year’s edition really showed us that APEX is far from ever going away! It has great potential for a very wide range of businesses and allows both developers and end-users to maximize their needs in every way.

Using a database link with heterogeneous services in APEX

Last weeks I ran into some issues with APEX, so let’s take some time to write something about them.

The most recent is about heterogeneous services (also referenced as HS). Here is the case:
We have an Oracle DB instance with some APEX applications and in one of those applications I need data from a MS SQL server database. Not a very good idea, but the set-up couldn’t be changed and both databases can handle it, so I gave it a try.

Our DBA installed heterogeneous services and created a database link between the two databases. Querying the necessary views was pretty easy (I only needed a couple of synonyms for ease of maintenance) and data came in faster than expected.

The next step was to query these views with APEX.
Because I created Oracle views on the views of MS SQL server, this was also very straightforward for APEX.

After a while however, there were issues in other applications and also the APEX builder itself raised errors while uploading files and importing applications. The following error returned:

ORA-02047: cannot join the distributed transaction in progress

, usually accompanied by some other ORA error codes. Not a very descriptive error message. A colleague pointed out to me that it has something to do with database links and heterogeneous services.

So, what is the problem?
APEX starts a new database session for my application. In this session the database link to the MS SQL server database is opened and data is queried. Since APEX constantly changes and re‑uses database sessions (this technique is called session pooling), the database session is released after a while and put back in the session pool until another application needs a new/another session. Because the database link in my session is only used for querying, there is no line of code that closes the database link so it remains open, even when the session is put back into the session pool. When another application takes this session from the pool, there might be issues with specific transactions, like setting a context (e.g. setting NLS parameters), because Oracle wants to perform this operation on both the Oracle database and the linked MS SQL server database. Because an NLS parameter is set when uploading a file, the end user receives the ORA-02047 error.

What can one do to prevent this from happening? There are several solutions for this issue:

  1. Don’t use database links with heterogeneous services. This is the easiest solution but isn’t always possible.
  2. Only access data over a DB link with HS in a package, function, procedure,… and close the DB link at the end of your block of code, which is not always possible either.
  3. Close any open database link, or only the specific one, when you put your session back into the session pool.

For the third solution, something interesting is implemented in APEX 4.1.

At application level, you can define some PL/SQL code that should be executed when initializing or returning a session from/to the session pool.

You can find this section under Shared Components  >  Security Attributes. All the way at the bottom is the section ‘Database Session’:

In earlier versions of APEX, something similar was already available for VPD (Virtual Private Database) but this only allowed execution of code at session initialization. The APEX development team has now expanded this functionality for any PL/SQL in general, so not only VPD-related, and both at session initialization and returning.

For my issue, all I had to do was write some code to close the database link when it was still open. I created a function in my application database schema which returns the number of open database links with a specified name:

CREATE FUNCTION is_dblink_open(dblink  IN VARCHAR2)
RETURN INT
AS
  l_cnt   INT;
BEGIN
  SELECT COUNT(db_link) INTO l_cnt
  FROM v$dblink
  WHERE db_link = dblink;
  RETURN l_cnt;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RETURN 0;
END;

Before you create this function, don’t forget to grant access to v$dblink:

GRANT select ON v_$dblink TO my_app_schema;

Execute with SYS. Note the v_$ instead of v$! (more info: http://goo.gl/euxcs).

All that was left is to use this function in the Database Session Clean Up Code:

DECLARE
  l_dblink VARCHAR2(100) := 'MY_SQL_SRV_DBLINK';
BEGIN
  IF is_dblink_open(l_dblink) > 0 THEN
    DBMS_SESSION.close_database_link(l_dblink);
  END IF;
END;

Or if you want to be sure that all open database links are closed, you could use:

BEGIN
  FOR l_db_link IN ( SELECT db_link AS name FROM v$dblink ) LOOP
    DBMS_SESSION.close_database_link(l_db_link.name);
  END LOOP;
END;

Special thanks to my colleague Jan for pointing out to the HS problem and to Patrick Wolf for helping me on this issue through the Oracle APEX forum (read the full post here: http://goo.gl/vVoeb).

This way, I hope to help out other people too when they run into this issue.

Get triggering element in dynamic action

Last week, I ran into a problem with a dynamic action in APEX 4.0.

I have a series of 8 images of persons which I generate with PL/SQL. They are all speakers on a conference. Every image is clickable and should insert the name of the speaker in the database.

I gave a class “speaker” to every image. The ID of the image is the primary key of the person in our database. By doing this, I am able to create a dynamic action which I can bind to the JQuery selector ".speaker".

In this case, I wanted to use AJAX to perform my actions so I created an AJAX callback insert_speaker which contained the PL/SQL block with the insert command. Based on the PK of the person, the name of the speaker will be inserted into the database, together with some extra information.

In my dynamic action, I added a true action of type “Execute JavaScript code”. Here, I ran into a problem: how do I get the ID of the image the user clicked on?

On the internet, I found two solutions with JQuery to get the ID of the triggering element of an event:

$(this).attr("id");

and

event.target.id;

There is a little difference between these two, found it out here, so I tested both and concluded I needed event.target.id.

I completed the JavaScript block with the AJAX request, the ID parameter and the other lines and tested the page.
Everything worked perfectly. Well done, I tought…
…untill I tested my page with Mozilla Firefox. Firebug returned an error ‘event is not defined’.

Hmm…and it all worked fine in Google Chrome (I use this browser for application development)? Strange.
After a while of searching, I figured out there is another solution, something specific to APEX:

this.triggeringElement.id;

Sounds logical, but it isn’t when you have no idea this line can be used in APEX.

The only official documentation I could find about this, is the help text of the textarea labeled “Value” in the When block of a dynamic action.
Help text

When I changed

event.target.id;

to

this.triggeringElement.id;

in my code, the page works fine in both Google Chrome and Mozilla Firefox.

For further development with APEX 4.0: make sure you test your pages in multiple browsers!
The example above states  very well that not all browsers interpret code the same way!