Redirect to URL

Redirect to another page within APEX, or to a new URL, using PLSQL.

The code may be useful in a before header process.

apex_application.g_unrecoverable_error ensures that any further rendering of the page, or processing on that page, stops.

 owa_util.redirect_url('f?p=' || v('APP_ID') || ':HOME:' || v('SESSION'));
 apex_application.g_unrecoverable_error := true;

An alternative way to stop processing is to use


The preferred way of doing this is now using APEX_UTIL.REDIRECT_URL

APEX_UTIL.REDIRECT_URL('f?p=' || v('APP_ID') || ':HOME:' || v('SESSION'));

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.

Firefox addons for developing in APEX

My favourite browser to use when developing in APEX is Firefox. The browser itself is superb but the main advantage is the ability to use addons. Without these addons I would find developing custom Javascript , HTML or CSS in APEX very tedious.

Here are a few of my favourite addons

Allows you to alter Javascript,HTML and CSS live without refreshing the page. Also very useful at monitoring the Ajax communication taking place between the browser and the server.

Web Developer
Similar to firebug, better in some ways worse in others. Whichever is better is a matter of opinion.

Has various colour tools but the main strength is that it gives you an eyedropper that can grab the colour of anything on the screen. It then lets you copy that colour to your clipboard in a variety of formats, hex, rgb etc.

IE Tab
Allows you to quickly see how the current web page would look like rendered using Internet Explorer