Recently I’ve been installing the OATOA (Oracle APEX on Tomcat with ORDS behind Apache) stack a number of times on both Oracle Linux 7 and CentOS7.

In the past this was no problem, Tomcat came up and a request to http://myserver:8080/ords/ gave me the expected APEX login screen.

However since Oracle Linux 7 and CentOS7, it seemed that Tomcat started before it could find the Oracle database. A simple restart of the tomcat service would do the trick. On my virtual machine (recently switched from VirtualBox to parallels btw) meant for doing presentations no problem at all.

systemctl restart tomcat

Now I’m playing around with “droplets” at DigitalOcean, just another name for virtual servers in my opinion, but they have great features at a reasonable price. Take a look:

But my droplets showed the same behaviour as my parallels virtual machine. I first had to restart tomcat to get a working system.

You all should know that I’m not a linux guru (really I’m not), so broke my brains about this for a looooong time.

Yesterday I finally managed to get this working. The idea is to make the tomcat start-script wait until it can see “something” on port 1521 (the db listener) and only then continue to start.

Open the file /usr/libexec/tomcat/server

nano /usr/libexec/tomcat/server

now add some lines of code _before_ the line that starts with MAIN_CLASS like this:

. /usr/libexec/tomcat/preamble
# .=.=.=.=.= START Make tomcat wait on oracle .=.=.=.=.=
while netstat -lnt | awk ‘$4 ~ /:1521$/ {exit 1}’0
  sleep 10
  let i+=1
  if [ “$i” -gt “5” ]
    break    #Abandon the loop.
# give Oracle some slack to also start the database
sleep 10
# .=.=.=.=.= END Make tomcat wait on oracle .=.=.=.=.=


add the obvious lines to your script.

What is happening?

The line “netstat -lnt | awk …” tries to find the string “:1521” in the result of a “netstat -lnt” command. This would indicate that something is listening on that port. In our case it will be the Oracle Listener.

If it does not find an open port 1521, it will sleep for 10 seconds, increment a counter (“i”) by 1 and do the loop again. The loop will loop a maximum of five times to prevent an endless loop.

After the loop completes we give the Oracle database another 10 seconds to get started.

That should do the trick. It does for me (I use the same code at digitalocean as well as my parallels virtual linux server.

I’m sure linux must have some system in place that could do the trick as well (make one service dependable on the other) but I haven’t found an easy one to use. If someone could help me out here, feel free to leave a comment.




When logging into the Oracle Application Feature Request application you will see the following announcement:

Announcing the creation of the Feature Advisory Board

A selection of APEX community experts have agreed to be members of the Feature Advisory Board. They will assist the APEX Development Team to prioritize feature requests. The board members will be reviewing features based on the total number of votes, so please enter your votes for all of the existing features.




Source Article from
Oracle Application Express Feature Advisory Board
Oracle & Apex Geekery
And another thing …

Follow-up on my article: “Who are you?” – client device Categorizr for APEX

About a year ago I wrote an article on how to determine by what kind of device your APEX application is accessed: desktop, tablet or smartphone. The solution is based on a package interpreting the HTTP_USER_AGENT environment string.

This package (categorizr) in combination with APEX offers a few functions regarding the client device type (isDesktop, isTablet, isMobile, …) that can be used in conditions or anywhere else in your PL/SQL code. Now I wrote a complimentary plug-in to determine some viewport properties: ViewportHeight, ViewportWidth and Orientation and put these values into page items in session state, updating the values (in session state) each time the browser resizes or changes orientation. Additionally, I added a an event to trigger an (advanced) dynamic action: Categorizer Resize Event.

In fact, both components, the package and the plug-in, can be installed and used separate. But when installed together, the categorizr package offers a two new functions:


and three global package variables:

  • g_viewportWidth
  • g_viewportHeight
  • g_viewportOrientation


Just install the plug-in by importing the file categorizr-plugin.sql.

Install the package by running the categorizr-package.sql in the database schema associated with your application.


This region plug-in is ideally used in page 0/global page but can be used in just one or more designated pages, as long as you do not combine both approaches.

Create a new region of type plug-in and choose “Categorizr”. Make sure you use “no template” for this region, or an empty region will be displayed on your page(s).


The plug-in has three attributes to specify the names of the (hidden) page items to create for holding the viewport width, height en orientation. Just keep the default, which prefixes the items with the number of the current page (using the placeholder #PAGE_ID#).


Once having this region on your page 0, you can access the page items (i.e. P0_VIEWPORTWIDTH, …) in your SQL and PL/SQL or use the JavaScript variables viewportWidth, viewportHeight, viewportOrientation.

You can download both, the package and the plug-in here:




Source Article from
How are you? – Categorizr for APEX part 2: the Plug-in
Oracle & Apex Geekery
And another thing …


Development Team Status Report from David Peake during OGh APEX World

Yesterday (April 9th, 2013) David Peake opened the Dutch OGh APEX World conference with a keynote presentation. As expected, he talked about future the future release of Oracle Application Express, version 5. Another very interesting part of David’s presentation focused on the architectural aspects of APEX running on the upcoming 12 c release of the database. Here’s my summary. But before you start basing any business critical decisions on this blog post, I have to mention thatoracle_save_habour

  • everything I’m writing in this article, may or may not be included in future releases of Oracle products
  • is my personnel view or opinion and that I’m not related in any kind to Oracle Corporation



The APEX development team is currently in the process of investigating functionality and possible solutions to include into APEX 5, and it seems that some of the features David was talking about were more idea’s and general directions. There was no live demo, understandably, just a few screenshots and a very interesting mock-up of the builder interface.

APEX on Oracle DB 12c

APEX version 4.2 will be preinstalled in the 12c root database. All pluggable databases (PDB) of this root instance will be running the same APEX version, but are isolated instances. The first thing after installing a 12c DB, probably, is upgrading the APEX to the latest release, currently 4.2.1 (there will be a 4.2.2, David told us). Upgrading APEX in 12c, when installed in the root database will be very easy, as all PDB’s get upgraded at the same time. For upgrading an 11g APEX installation to 12c, there will be scripts provided to convert the it into a root installation.

If you want to run different version of APEX in different PDB’s on the same 12c root instance: you can. Oracle will provide scripts to remove the root installation of APEX, so you can install independent repositories and versions of APEX in each PDB. It is more administration, but as a developer, I’m glad to get this possibility.

There are some new 12c DB features, that will be supported by APEX 4.2.something:

  • VARCHAR2 32k support. As HTML text fields didn’t care much about field length anyway, this is manly an enhancement on collections.
  • Identity Columns. This means, you won’t need to specify a sequence or function to populate a PK, because this will happen automagically.
  • Invisible Columns: will not be displayed by default.No values will be inserted when basing forms on this type of columns. You can manually add code to your page to show the value of an invisible column.
  • Data Redaction. This is a feature that does not need much of a change in APEX itself, as data is masked in the database, depending on policies. But there will be a change in the SQL workshop to support administration of these policies.


We all know the Oracle Application Express Statement of Direction, so I don’t have to list features already mentioned there. David pointed out, that the development of APEX 5 has to main focuses:

  • Improve Developer Productivity
  • Quality

Here what drew my attention during David’s APEX 5 possible feature presentation.

  • we will be able to declarative define Modal Dialogs in APEX 5. We even can stack multiple modal dialogs.
  • the APEX dev team is thinking of enhancing the Builder interface and the concept seems to be inspired by interfaces we know from SQL Developer, JDeveloper and other similar IDE’s.
  • a tree navigation on the left hand side to access page components
  • possibility to switch to a component view instead of tree view
  • a large layout editor (welcome back) in the middle of the page with predefined components to (drag & drop) place into your page layout
  • a property editor showing the properties for the selected page region/item.

Revamped APEX 5 Builder Interface; Artist Impression (i.e. mine)

  • a new tabular form component based on jQGrid with inline editing ability:

jQGrid screenshot (not from actual APEX screen)

  • PDF Printing, using APEX Listener 2.0’s FOP support. Integration will be more declarative, and the possibility to specify formatting options through templates, break control and master/detail layouts.
  • further extension of WebService capabilities, like publishing and SOA integration. Allowing to connect with remote Oracle DB’s through WS, as an (better) alternative to database links.
  • the rest pretty much was known from the SOD …

Wrapping it up

All together, I think version 5 will be a release to look forward to, from a developer perspective. It looks like, some of the current shortcomings, like master-detail-detail and multiple interactive reports on one page, will be tackled by the APEX development team. Especially the revamped builder interface is a very interesting idea, but, when keeping the current Tree View AND Component View, there will be a lot more pages/code to maintain by the dev team.

Oracle currently is working on enhancing version 4.2 (12c DB integration) as well as version 5. With 12c expected for somewhere in 2013, we can easily guess the priorities. APEX version 5 will not be spectacular with respect to end-user functionality, but consolidate the development of the past years and enhance existing functionality (mobile: table support, IA Reports: multiple on one page, Master/Detail/Detail, …)

Source Article from
APEX 5 – New features that may or may not be included
Oracle & Apex Geekery
And another thing …

Oracle Application Express for Mobile Web Applications, written by Roel Hartman, David Peake and me, shows you step-by-step how to create your very own web application that will look and act as a native mobile application.


Oracle Application Express for Mobile Web Applications is an action driven book, taking you by the hand through all required steps in building your very own web application that will run on phones, tablets, and other mobile devices. Because you’ve built every piece of it yourself, you will know exactly how every tiny part is used and how you can tweak it to your own – or your customer’s – taste.
Oracle Application Express is widely known in the Oracle community as a great tool for creating web applications suitable for desktop browsers. Features have now been added to open up the world of mobile browsing, bringing the simplicity and expressiveness of Application Express to bear in developing applications to run with an almost-native look and feel on platforms such as iOS, Android, and Windows Phone.
Oracle Application Express for Mobile Web Applications helps you translate your knowledge of Oracle Application Express into developing for mobile devices. The book and its running example provide all the knowledge you need to create professional looking mobile web applications.

  • Takes you through building a mobile web application from start to finish.
  • Gives insight into the components necessary for a professional looking mobile application.
  • Helps you become an even better and more all-round Oracle Application Express developer.
What you’ll learn
  • Build mobile applications to run from iPhones, iPads, Android devices, and Windows-based phones and tablets.
  • Make enterprise reporting and business functions accessible to mobile devices.
  • Customize the look-and-feel of your mobile applications.
  • Respond to gestures such as swiping and pointing, and to changes in device orientation.
  • Enhance mobile applications through the use of plugins.
  • Deploy applications into the Apple and Android stores.
Who this book is for

Oracle Application Express for Mobile Web Applications is aimed at Oracle Application Express developers wanting to develop and deploy applications for use on mobile devices. The book is also useful to any developer in an Oracle Database environment who is looking for a cheap and cheerful way to extent business functions and reporting to mobile devices.

Source Article from
Oracle Application Express for Mobile Web Applications – Book available now
Oracle & Apex Geekery
And another thing …

Wow, I broke my brains over this for the last three days.

I installed the apex listener as mentioned in the manual. It mentions you can use Java 6 Update 20 JDK or later.

However, what they mean is that you should use Java 6 JDK, and you can use update 20 or later.

What I did in my stupidity is to use Java 7. That’s later then Java 6 upd 20 right?


If you use Java 7 your listener might work (which was the case in my situation) but connecting to it using SQL Developer is a “no go” 🙁

Be warned.

regards, Richard

For a client I needed to develop an Apex page were some items were set when the user changed the value of a select list. Because of user-experience I did not want to do a post to the server. Instead I wanted to use Javascript and Ajax. This blog tells how I implemented this.

Thanx to Roel’s comment, there is a more easy way; just make a (advanced) dynamic action on the list-item which sets the items based on a select statement. So, this blog is just to get some more depth in javascript-code & JSON.

Basically the page consists three items:

  1. A select list were the user can pick a product.
  2. A display item were the price of the product is shown.
  3. A display item were the supplier of the product is shown.

Task to accomplish
When the user selects a product, the values of price and customer had to change to the corresponding values in the database.

Steps to accomplish this task:

  1. Create a procedure which sends the price and supplier in Json-format depending on the product-id
    create or replace procedure get_product_info (p_product_id in number)
      t_sql varchar2(256);
      t_sql := 'SELECT price, supplier FROM products WHERE id = '||p_poduct_id;
      /*Print JSON result set*/
      apex_util.json_from_sql (t_sql);
  2. Create a pl/sql page-process productInfo (point: on-demand)
    get_product_info(p_product_id => :P3000_PRODUCT_ID);
  3. Add javascript to your page
    function ChooseProduct()
    //the application proces to be executes (prepare ajax-call)
    var get = new htmldb_Get(null,&APP_ID., 'APPLICATION_PROCESS= productInfo',&APP_PAGE_ID.);
    //set the value of product-id to the value of the item 
    //exceute the ajax-call and get the reponse in JSON format
    var gReturn = get.get();
    // make from the response string a JSON object
    var obj = eval ("(" + gReturn + ")");
    //set the value of the items, based on the response
  4. Add a dynamic action to the select list which executes the javascript function “ChooseProduct();”, when the value is changed.

That’s it!
Sergei Martens

Got it from Andy’s Blog:

Within ApEx PL/SQL Processes, regions and items you can use the bind variable syntax (:PX_MY_ITEM) both to read and set the value of that item held in session state. Like so:

:PX_MY_ITEM := 'wibble';
l_my_local_variable := :PX_MY_ITEM;

However, in stored PL/SQL packages, procedures and functions you cannot use the bind variable syntax. Rather, you must use the v(‘PX_MY_ITEM’) syntax. But this is read only.

So how do you set the value held in session state for a given page item from within a stored package, procedure or function?

The answer lies in the set_session_state procedure found in the APEX_UTIL package. E.g.

        p_name  => 'PX_MY_ITEM'
      , p_value => 'wibble');

I’ve discovered that if you use an item-plugin for the first item of a page and the property ‘Cursor Focus’ is set to ‘First item on a page’ the cursor will not be focused at the first item on the page but on the first item that is not an item-plugin.

It looks like that item-plugins are not taking into account when the cursor should be focused on the first item on the page. APEX will add first_field('<name of the first item that's not a item-plugin>'); to  the code that will be executed  when the page loads.

Workaround: set the property ‘Cursor focus’ to ‘Do not focus cursor’ and add first_field('<name first item>'); to the property ‘Execute when page loads’ of the page.

I’ve got a reaction from Patrick Wolf by e-mail that i want to share:

A better approach is to change the item-plugin: the item-plugin has to set is_navigable to true in the render result record to support the “First item on page” feature.