Oracle APEX Interactive report based on PLSQL function

With the latest release of Application Express, Oracle have included a brand new reporting widget called Interactive Reports. They have managed to make it so simple to have fully functioning report where the only effort from the developer is creating the SQL query. Unfortunately Oracle have failed to allow the report to be based on a PLSQL function returning the query. This omission is a serious oversight by Oracle, as all my reports use this technique. Until Oracle update APEX to include this feature here is a workaround to display an interactive report based on a query stored in the database.

Create a collection based on a query. The code for this should be placed in a before header process

p_collection_name => ‘IR_TEST’,
p_query => function_returning_query );

Step 2.
Create an interactive report querying a collection.

Select *
From apex_collections
Where collection_name = ‘IR_TEST’;

Step 3.
You will notice that if you run the report that the column headers do not correspond to your dynamic query. This is because the IR is querying the collection and therefore using the column names from that. To resolve this, create hidden page items(eg P1_COL_HEAD1, P1_COL_HEAD2 etc) to hold the correct column name. Ensure that you create enough to hold the maximum expected columns. (I create 50 since this is the maximum a collection can hold). For each of these items replace the cxxx column header text in the report attributes for the relevant page item. eg. C001 for &P1_COL_HEAD1. and C002 for &P1_COL_HEAD2. (remember to include the dot).

Step 4.
Create a process that will populate the heading page items with the relevant column name values from the dynamic query. This can be done by splitting the select line using the commas and extracting the dynamic column headers.

Step 5.
Now that the column headers are displaying correctly we should hide any irrelevant columns. Because a collection has 50 main columns there can be many columns without any data, and because this report is meant to be dynamic we want the columns to dynamically display whenever there is any data in them. Go into the report attributes and go into every column starting cxxx and set a conditional display type of exists and enter the following SQL. Replacing c001 with the relevant column header.

Select 1
From apex_collections
Where c001 is not null;

Finally set the conditional display to never for the following columns collection_name, seq, clob001, md5_original.

And that’s it! The method outlined above does seem a bit extreme to allow an interactive report to display a dynamic query but currently it is the only workaround I know of.

Manually set page success/failure message

APEX allows you to define a success or failure message when running a page process. It is also possible to do this manually using the following code

apex_application.g_print_success_message := ‘Message you want to display’;

for the message to show, ensure that you tick the checkbox on the relevant branch to “include process success message”

How to stop the user from clicking a page submit button more than once

The standard way to push the page data back to the APEX server is to use an HTML button. One issue with HTML buttons is that they can be clicked more than once, and under certain circumstances this can cause issues. To ensure that the button can only be pressed once you can do the following.

alter button settings so that inside the URL redirect section the URL target value is set to


Note that the value inside doSubmit(”) is the same as the button name.

You can even go one step further and replace the text of the button to something relevant like “Submitting….”. The following code does this


The button now looks like this when it has been clicked

Thats it, the button is now coded so that the user can only click it once to submit the page. Any further clicks are ignored.

Basics of AJAX

In Application Express(APEX) it is possible to communicate with the database without submitting the entire page. This is extremely useful when your APEX application is being run on a very slow network. This on demand communication is possible in APEX using Asynchronous JavaScript and XML(Ajax). Using this method sends only the minimum information required to retrieve a value from the database. I will explain using a simple example involving the famous emp table.

lets say I have a page that has three items on it

  • P1_EMPID. this will be a textfield used to enter the emp_id
  • P1_ENAME. this will be used to display the ename
  • SUBMIT. this will be a button that will submit the page. this page submission will fire off a process that will populate P1_DISPLAY with the relevant ename for the given emp_id

The issue with the above example is that the entire page has to be reloaded when in fact only one small section will be updated, namely P1_DISPLAY. the following example will demonstrate how to retrieve the name from the database without refreshing the page.

1. Move the database code from the page process and place it in an application process. Name the process “GET_ENAME” and give it a type of “On Demand”. example code is below

v_ename emp.ename%type;
select ename
into v_ename
from emp
where emp_id = :P1_EMPID;
exception when others then htp.prn(‘An error occurred retrieving the name’);

2. Create the following javascript function.

function get_ename(){

retrieve the emp_id from the page
var emp_id = document.getElementById(‘P1_EMPID’).value;

define the Ajax call. The only variable of note in this example is the
application_process, which I have set to be the same name as step 1.
var get = new htmldb_Get(null,html_GetElement(‘pFlowId’).value, ‘APPLICATION_PROCESS=GET_ENAME’,0);

add the value in P1_EMPID into the session value for P1_EMPID.
this is important as without this step the APEX server would not know
what value the user had entered

call the ondemand process and accept the returning ename

var gReturn = get.get();

set the field on the page to equal the ename retrieved from the database

document.getElementById(‘P1_ENAME’).value = gReturn;


3. Finally alter the SUBMIT button so that it uses a URL redirect and in the URL field enter

javascript:get_ename();return false;

and thats it, when you press the submit button it will now send the emp_id to the database and populate P1_ENAME with the relevant name. and all without refreshing the page.

obviously this is a trivial example but it gives an idea of the possibilities of Ajax.

Integrating jQuery into APEX

jQuery is an open source Javascript library that enables the developer to produce concise, cross browser Javscript quickly and easily.

To integrate jQuery into Application Express you must first download the library from For performance reasons i would recommend downloading the production version.

Once you have downloaded the file you have to upload the file into APEX. Do this using create image within shared components

Now that the jQuery library is stored within APEX you can access it using the following code

<script type=”text/javascript” src=”#WORKSPACE_IMAGES#jquery-1.3.1.min.js”></script>

(you can upload the file to a specific application. if you do so then you must use #APP_IMAGES# instead of #WORKSPACE_IMAGES#)

This code can either placed inside the specific page where it is needed, or accessible to every page by placing it on page zero. If you wish to put it inside a page the sensible place to put it would be in the html header

Or, personally I prefer to place it inside the page template. This can be found within shared components.

For specific uses of jQuery please refer to

jQuery datepicker

Oracle’s native datepicker is extremely primative and is in urgent need of an update. I do not like the fact that it pops up a new window, which in my experience can easily be lost behind other windows and forgotten about. Luckily there is a better solution out there, in the form of a jQuery datepicker. This does not require a new window and also does not require much to integrate into an APEX application. Here is a short guide on how to integrate jQuery datepicker into APEX.

The first step is to ensure that you have the jQuery library included in your application. Follow guidelines here to do this. Basically you upload the jQuery Javascript file to application images and include it in your app using the following code

<script type=”text/javascript” src=”#WORKSPACE_IMAGES#jquery-1.3.1.min.js”></script>

In addition to the jQuery library you must also download the jQuery ui package. this can be found at jQuery allow you to create a custom download. This has the benefit of reducing the size of the file while still giving you the features that you want to use. When creating the package ensure that you select the ui core and the datepicker widget


Once the file has been downloaded, upload the script to the application images and include the script in your application. This is done in the same as the core jQuery file.

As well as including the javascript file you must also include the following CSS files. These are stored inside the theme folder

  • ui.datepicker.css
  • ui.theme.css

Once these files are uploaded to the shared components > images, use the following code to include the css in your application

<link rel=”stylesheet” href=”#WORKSPACE_IMAGES#ui.datepicker.css” type=”text/css”>

<link rel=”stylesheet” href=”#WORKSPACE_IMAGES#ui.theme.css” type=”text/css”>

So far we have the following code in place to include the relevant Javascript and CSS files to allow you to integrate the datepicker

Before creating the datepicker field in the app we must define how jQuery will attach the datepicker functionality to items on our screen. This can be done on an item by item basis but for simplicity i will assign the functionality to all items with the class of “datepicker”



showOn: “both”,
buttonImage: “#IMAGE_PREFIX#asfdcldr.gif”,
buttonImageOnly: true,
dateFormat: ‘dd/mm/yy’



Finally create an item with a type of “text” and assign it a class of datepicker

now when you run the page you should have a date field with a datepicker popup that is infinitly nicer than the in built Oracle datepicker

view example here

Using APEX Authorisation Schemes In PLSQL

APEX allows the developer to define multiple authorization schemes for use throughout the application. Unfortunately APEX, by default, only allows the use of these schemes in certain areas. For example to decide whether a user can see a specific page

or whether a specific page item should be displayed

Luckily there is a way to use the authorization in PLSQL, this is done using the following code

v_bool := apex_util.public_check_authorization(‘AUTHORIZATION_NAME’);

The authorization scheme can now be used on its own, or in a combination with other schemes, as well as being part of more complex logic

jQuery Thickbox

A jQuery thickbox allows you to create a popup region without actually opening a new browser window or tab. The thickbox region can contain some content already on the page, usually hidden when the page is viewed normally, or the content of another page.

Before starting you must have included the jQuery library in your APEX application. For instructions on how to do this check the guide Integrating jQuery into APEX

Once you have jQuery installed successfully you must also download the specific files for the thickbox, these can be retrieved from Download the javascript, css files.

Include the javascript and css files in your application using the following code

<script type=”text/javascript” src=”#WORKSPACE_IMAGES#thickbox.js”></script>
<link rel=”stylesheet” href=”#WORKSPACE_IMAGES#thickbox.css” type=”text/css”>

To use the thickbox to display an existing region do the following

Create a region with “No template” and place the following in the region header

<div id=”thickbox_region” style=”display:none;”>

And close the div in the footer


Now create some content in the region to give the thickbox some content. For example I will create two items, P2_TEXT and P2_DATE. These items are purely dummy content.

Next, create a region to hold the buttons that will launch the thickbox region. This region does not require any specific settings.

Finally create the button that will launch this region. First create a button with the default regions, then once the button has been created alter the following settings.

For the optional URL redirect enter

javascript:return false;

And for the button attributes enter

class=”thickbox” alt=”#TB_inline?height=300&width=400&inlineId=thickbox_region”

You will notice from the alt tag that you can alter the width and height settings for the tickbox. For details of other settings visit

Now when you run the page and click the button the thickbox region should appear like the following example.

To view the above example click here

URL Parameters

In most cases the URL is automatically handled by APEX and the developer does not need to understand it. However with more advanced use of APEX comes the need to manually specify certain aspects of the URL. The following is a quick explanation of how the URL is constructed.

For this explanation we will focus on the f?p= part of the URL and explain each specific parameter


1 – The number or alias of the application you wish to run. The alias can be set in shared components -> definition

To access the current application id, use &APP_ID.

2 – The page number or page alias. The page alias can be set in the page attributes

3 – The session. to access the current session, use &SESSION.

4 – The request term that you want to pass to the destination page

5 – DEBUG. Decide whether the page is run in debug mode or not. either YES or NO

6 – This parameter can accept two values. RP = resets the pagination of the destination page followed by a comma separated list of pages where you wish to reset the cache. So for example if you want to reset pagination and reset the cache for pages 1 and 2 you would include “RP,1,2” for this parameter

7 – Comma separated list of page items to assign values to.

8 – Comma separated list of values to assign to the page items specified at position 7. The values must be in the same order as the page items are

9 – Printerfriendly. Determines if the page is being rendered in printer friendly mode. YES or blank

Creating A Popup Window

Standard behaviour in APEX is for a branch to link to a page using the current window (or tab). However there are times when it is desirable for the application to link to another page in a popup window. This may be popup help, like the standard help in APEX, or maybe a print preview page. The following is how to create a popup window.

To create the popup window I am going to use Javascript. This can be placed behind a button or text link, or it can be placed in an HTML region.

The code to create the popup is very simple (URL, windowName[, windowFeatures]);

The name of the popup window. This name is not visible by the user, and is only there for reference by Javascript. For the example below I have decided to use the name popup1. If was called again, using “popup1” as the windowName, then the contents of the popup would be refreshed with this new call. For this reason it is a good idea to use unique names for each window

In the example code below I will simply call page 200 without passing in any page item values. If you wish to pass in values, or pass in a request value then please check out the post on URL parameters

This allows the developer to define specific features for the popup window. For example, no scrollbars, the size of the window and whether certain menu bars are visible. For a full list of features visit here

The Javascript“f?p=&APP_ID.:200:&SESSION.”,”popup1″);

The above line of code will open a new window (or tab depending on browser settings) called popup1, containing page 200. Easy.

A couple of things to note when using popups.

You will see that I have passed in the current session value to the new popup. This is useful as it allows the window to have access to all your current session values, as well as knowing that you have passed authentication. One issue with using the same session is that the two windows could be able to access(or more importantly, alter) the same session values at the same time. This can cause unexpected results.

Therefore it is sensible to have the popup as a deadend in your application. For example this could be a single page containing a print preview of the current page, with no links to any other page. For this reason it is a good idea to use a template specific to popup pages. This template should be as blank as possible and have little or no navigation items in it.

Another issue is the use of popup blockers. In most cases it is not known whether the end user is using a blocker, and how that blocker will behave when the popup is called. Unfortunately there is no easy answer to this and must be addressed on a case by case basis.