Oracle Application Express Feature Advisory Board


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 …

How are you? – Categorizr for APEX part 2: the Plug-in

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 …

APEX 5 – New features that may or may not be included


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 – Book available now

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 …

APEX Listener favicon bug issue

When developing an application in Oracle APEX, I usually have opened two browser tabs, one for the builder and one for the application I’m working on. This works fine, even though both tabs are using different sessions.

The other day I noticed a strange annoying behavior using this approach: Each time I opened the application I was working on in the new tab, my builder session was expired. This behavior wasn’t related to the APEX version: it happened with my 4.2 installation running on a VBox as well as with my local installation running 4.1. I was rather puzzled, as I didn’t change anything on my local installation.

Here is what caused it (thanks to Christian Neumüller for explaining):

I ran into the so-called APEX Listener favicon-bug: On my 4.1 installation I was working on a new theme, build entirely from scratch, and on my 4.2 installation, I was playing around with a mobile application. What both applications (or better: themes) had in common: the lack of a favicon reference in the page template. One other thing both environments had in common: an APEX Listener stand-alone installation. Looks like the Listener tries to resolve a favicon location each time, and when not found, redirection the favicon request to the “webservers” root (the favicons default location). With the APEX Listener the “root” gets redirected to yourserver:port/apex, which is causing a reset of the session id.

Adding a favicon to your page template(s) HEAD section solves the problem.



Make sure you’ve got an image file at the specified location. I could reproduce the problem with APEXListener versions 1.1.4 and 2.0.
I was promised, the issue will be addressed in one of the coming versions of the APEX Listener.

Source Article from
APEX Listener favicon bug issue
Oracle & Apex Geekery
And another thing …

Cloudy Normal Button :hover fix

mouse_cursor_hoverMaybe you have noticed it, maybe not, but the hover effect of the Normal Button template in the APEX Cloudy theme does not  work in IE. It seems it hasn’t been implemented. So if you want the hover effect in IE, here is the CSS snippet you need to to add to your template:

.ie6 a.uButton:hover span,
.ie6 a.uButtonLarge:hover span,
.ie6 a.uButtonSmall:hover span,
.ie7 a.uButton:hover span,
.ie7 a.uButtonLarge:hover span,
.ie7 a.uButtonSmall:hover span,
.ie8 a.uButton:hover span,
.ie8 a.uButtonLarge:hover span,
.ie8 a.uButtonSmall:hover span,
.ie9 a.uButton:hover span,
.ie9 a.uButtonLarge:hover span,
.ie9 a.uButtonSmall:hover span {
background: url(#IMAGE_PREFIX#themes/theme_24/images/app_theme.png) 0 -400px repeat-x #DEDEDE;

This will only affect IE and will load a slightly lighter gray gradient background on the hover state of the normal button.

Credits go to Shakeeb. Thanks!

Source Article from
Cloudy Normal Button :hover fix
Oracle & Apex Geekery
And another thing …

Book Release: “APEX Best Practices” – A First Impression

Oracle APEX Best PracticesToday (nov. 6th 2012), the book ‘APEX Best Practices’ written by Learco Brizzi, Iloon Ellen-Wolff and Alex Nuijten was finally released. I was curious about the book, for several reasons:

  • I promised the book to the winners of my FifApex competition earlier this year
  • I know all three authors and heard some stories about the process of making it
  • As an APEX developer: what ‘Best Practices’ will the book advice

Here is my first impression:

There is more to APEX then just building pages, and this is where this book comes in handy. Installation, set-up, maintenance, versioning, deployment, printing, are all subjects elaborated in this book. ‘APEX Best Practices’ will help you planning and setting up your environment. Also the book talks about creating pages and reports as well, it’s not the usual step-by-step tutorial. It focuses on the structure of an application and the development process in general.

The book contains an extensive chapter (probably written by Alex) on how to leverage the Oracle database functionality in an APEX application. A must read, in my opinion. After all: the best thing about Oracle Application Express is the Oracle database it’s build on.

One advantage of the delayed release: the authors managed to add a chapter (well, actually an appendix) about Database Cloud Service, APEX 4.2 and RESTful Web Services .

It’s probably not the first book you would grab from the shelf when starting to learn building applications with Oracle Application Express, but you should definitely read the book before starting your first real APEX project.

Book Details

Language : English
Paperback : 298 pages
Release Date : November 2012
ISBN : 1849684006
ISBN 13 : 9781849684002

Source Article from
Book Release: “APEX Best Practices” – A First Impression
Oracle & Apex Geekery
And another thing …

Remember Me – APEX Autologin

Let’s start


CREATE TABLE my_custom_users(
   username   VARCHAR2(25 BYTE)
, password   VARCHAR2(250 BYTE)
, token      VARCHAR2(25 BYTE)

INSERT INTO my_custom_users(username, password, token)
VALUES (‘DEMO’, my_cust_auth.encodeit(‘DEMO’, ‘demo’), NULL);


Step 1: setting the cookie during loginremember_me_01


Step 2: using the cookie on return

Now, when do we need to read the “REMEMBER_ME” cookie again? Every time the visitor returns to your site/application, has not signed in yet (obviously, as we want to do this automagically) and the cookie is set and holds a token that is known in the user table (assuming that the user is the same again!). I want to perform the check, regardless of the page visited is a public page or a page that requires authentication. The event that should be triggered, if the conditions are met (cookie set and valid, user is public), is the a automatic login, similar to the original login. To perform the check, I will use “PAGE 0”, but lets first create the autologin functionality to be called:
remember_me_04To realize the autologin, I create a new page (103 in example application). This page only contains a “On Load – Before Header” process and one page item. The process only fires, when the request name is “AUTOLOGIN” and calls the build in standard APEX login procedure provided for custom authentication. It uses the page item to “P103_TOPAGE” as target page after successful login, which I will set on the triggering process/branch on page 0, to return to the page the user actually requested in the URL. The username is derived from the cookie (the token belonging to one unique user) using the call OWA_COOKIE.get (‘REMEMBER_ME’); in a stored procedure.

Step 3: automagically do it


I added an extra condition checking for the current page ID to be less or equal to 101, to prevent page 103 from recursively calling itself. Page 102 will be this demo application’s special logout page (see next section). All “normal” pages in this application are assumed to have ID’s in the rage of 1 to 100. You definitely should modify this condition to meet your actual applications page ID ranges.remember_me_08

Step 4: forget me

remember_me_07We need to offer the user a way to logout and remove the cookie. I create a new page, 102, containing one “On Load – Before Header” process and a branch.

The process removes (expires and replaces value with empty string) the cookie and performs the actual logout using WW_FLOW_CUSTOM_AUTH_STD.LOGOUT. The branch will take the user to the (public) Home page (1).
I then specify to use page 102 as the Logout URL of my Authentication Schema.

Demo and Download


Possible Enhancements:

The whole mechanism is a rather simple and naïve approach. I just wanted to explain the basic principle of it. There are lots of enhancements and improvements one could think of, and actually, while writing this post I thought of some myself:

  • using pre/post function call of Authentication Schema instead of page processes
  • integrate autologin logic from page 103 to page 101
  • cookie name application variable or dynamically generated
  • investigating the possibility of an autologin authentication plugin

Source Article from
Remember Me – APEX Autologin
Oracle & Apex Geekery
And another thing …

Dynamic Date Range in APEX Datepicker – no Plugin required


The Basics: jQuery Datepicker in APEX


Let’s Get


Set it

Well, it works! This is what the Datepicker looks like after setting the min- and maxDate.

Wrap it up

If now we combine getting the values of the Min./Max. Date page items and setting the the minDate/maxDate options of the “Actual Date”  item, put this into a Dynamic Action, lets say: “execute JavaScript on the Change events of the first two date fields”, we should have exactly the intended behavior.


Do the same for P1_MAX_DATE using the code:

$(“#P1_DATE”).datepicker( “option”, “maxDate”, $(“#P1_MAX_DATE”).val());

Just two statements! See for yourself and visit the demo page. Of course the variations of this approach are countless, but the basic two statements will remain: getting the value and setting the range options.

Have fun APEX-ing.

Source Article from
Dynamic Date Range in APEX Datepicker – no Plugin required
Oracle & Apex Geekery
And another thing …

APEX Tabular Form: focus cursor on first element of new row

focus0I just got the question from a fellow developer, how to focus the cursor to the first input field of a newly added row using the ADD button in a standard APEX Tabular Form. Actually, I would expect APEX to do this automagically. But it doesn’t. To implement this behavior is actually really simple.



For this example I create a simple tabular form based on the demo_customers table:


This is what the form looks like when you click the “Add Row” button. As you can see, none of the input field has focus at this time. To focus the cursor on the first field of the new row (Cust First Name), I somehow have to change the behavior of the “Add Row” button. Let’s have a look at it’s definition:


The click-action of the button is actually a JavaScript call: addRow();. Naturally I want to keep this action, but after this, I want to start an additional action that should set the focus to the input field. To be able to have multiple actions performed when the button is clicked, I will change the Action definition to “Defined by Dynamic Action”:


Now I’m going to add a Dynamic Action to the “Add Row” button. First action will be the original JavaScript call to add the row, second action will be setting the focus. This is how the definition will look like after I added the actions:


Creating the Dynamic Action and the first True Action I will do with the Create Dynamic Action Wizard for the button:





Now I have implemented the original behavior.

Next I want to focus the cursor on the first text-field of the added row, once I click the button. I know there is a build-in Dynamic Action to set the focus on an element. I just need to know which element. Let’s have a look at the HTML code of the generated APEX page, especially the the text-field element we wish to target (I used the Chrome Developer Tool here):


As you can see, the name of this element is f02 and the id f02_0008. Because the ID can change depending on the number of rows displayed, I choose to work with the name attribute, which stays the same. The problem is, all “Cust First Name” fields do have the same name. Luckily jQuery offers a way to select the last element in an array of element with the same name and luckily, APEX Dynamic Actions support jQuery selector syntax. So here is, how I define the focus action for my “Add Row” button:



As you can see, I select all elements having the name attribute equal to f02 and :last allows me to select the last element of these. Here is a screenshot from the result (after clicking the “Add Row” button) with focus on the first text element of the last, newly added row:


Source Article from
APEX Tabular Form: focus cursor on first element of new row
Oracle & Apex Geekery
And another thing …