Checking if the current user is logged into Application Builder

If you want conditional logic in your PL/SQL dependent on whether the current user is also logged into the APEX Application Builder, this can be achieved by reference to:




This will have a value if the user is logged into APEX Application Builder, otherwise it will be null.

Related documentation:

Categories: Uncategorized

Working with APEX 5 Dialogs

February 17, 2017 Leave a comment

APEX 5 introduces native dialogs, both modal and non-modal. This article discusses:

  1. How to enable dialogs in your APEX 5 application if they are not already enabled.
  2. How to open a dialog.
  3. How to close a dialog, returning values from that dialog.
  4. How to respond to the closing of a dialog.

How to enable dialogs in your APEX 5+ application if they are not already enabled.

To check if Dialogs are already usable within your Application, start the Create New Page wizard and see if you can specify a Page Mode value of “Modal Dialog”. If you can then great, you don’t need to complete the steps in this section.

If you can’t, follow the steps below to enable the use of Dialogs.

NOTE: You won’t need to perform these steps if you’re using the Universal Theme. However, it is not necessary to be using the Universal Theme to use APEX’s native dialogs.


Above: If you can select Modal Dialog from the Page Mode select list then you do not need to complete the steps in this section.

Enabling APEX 5 native dialogs in your application

  1. Firstly, you need a Page Template which will be used as your Default Page Template for Dialogs. Within Application > Shared Components > Templates locate a suitable Page Template which already exists. You want to choose a Page Template which contains none of the components which would not be suitable for a dialog e.g. tabs, nav bar etc..
  2. Make a copy of this Page Template, calling the newly-created copy something like “Dialog Page”.
  3. Set the Template Type of this new Page Template to “Dialog Page”.dialogs2
  4. When you try to save the the new Page Template you may see errors such as the following:dialogs3To rectify these, locate the three fields mentioned and paste in the default values shown for each, as shown below.dialogs4
  5. Navigate to Shared Components > Themes. Open the the current theme for your application.
  6. Navigate to “Dialog Defaults” and select your newly-created Page Template from the Dialog Page select list. Click Apply Changes.dialogs5
  7. You should now be able to create new pages specifying their Page Mode as “Modal Dialog” or “Non-Modal Dialog”. You should also be able to modify the Page Mode of existing pages to turn them into dialog pages.

For more information about Dialog Templates in APEX 5:

How to open a Dialog

There are a couple of ways of opening a dialog page in APEX 5+.


The simplest way is to use any of APEX’s built in methods of generating navigation.

For example, setting the action of a Region Button to “Redirect to Page in this Application” and choosing a Dialog Page.


Alternatively, if you need to open a dialog using Javascript, you can use the following method:

a) Create a hidden page item which is going to hold the prepared URL of the dialog page you want to open. e.g. P35_DIALOG_URL

b) Populate this item with an on load pl/sql page process:

:P35_DIALOG_URL := APEX_UTIL.prepare_url(
, p_triggering_element => ‘$(”#openDialogIcon”)’

The first argument passed to APEX_UTIL.prepare _url here is easy: it’s the URL of the dialog page.

The second argument, p_triggering_element, is a string representing a jQuery object which will be identified as the Triggering Element which opened the dialog.

Note that you must use $(‘#someitemid’) and NOT $(“#someitemid”) (i.e. single quotes instead of double quotes to surround the selector string).

The jQuery object you specify here is important as it’s the item against which the apexafterclosedialog Javascript Event will be registered. You can see an example of this later in this article.

This call to APEX_UTIL.prepare_url will return a string of the following form:

javascript:apex.navigation.dialog(‘f?p=400:38:995688545561::NO:RP::\u0026p_dialog_cs=BA6YtuVKy9_VwUU3hN7x1vcfehc’,{title:’Select desired Item from List’,height:’600′,width:’700′,maxWidth:’false’,modal:true,dialog:null},”,$(‘#openDialogIcon’));

c) Create an On Page Load Dynamic Action with a Javascript action to assign this Prepared URL with an tag’s HREF attribute. E.g.:

// Wrap an tag around the icon which opens the Select Item dialog.
// Set the href of this new tag to be the URL
// javascript:apex.navigation.dialog(…) style value required to open
// the dialog.
$(“#openDialogIcon”).wrap( ‘<a href=”‘ + $(“#P35_DIALOG_URL”).val() + ‘”></a>’ );

How to close a dialog, returning values from that dialog.

1) You can close a Modal Dialog in one of three ways:

a) A Close Dialog Page Process (submit the page to fire this as you would any other Page Process).

b) A Close Dialog Dynamic Action

c) Call Javascript of this form:


Where the first parameter (true) indicates whether the dialog is modal or not and the second parameter is an array of items in the dialog whose values should be available in the parent once the dialog has closed.

See for more details on this Javascript function (i.e. apex.navigation.dialog.close).

2) The important concept, regardless of which of the above 3 methods you choose to close your Dialog, is the Items to Return value. This is the correct way to pass back items to the calling/parent page.

As John Synders points out here (

John Synders on the philosophy behind dialogs:
“The normal pattern for using dialogs is that it is OK for the calling or parent page to know about the dialog page, but not the other way around. The dialog should not know the context from which it is used. If a dialog page knows about the page it is called from then it limits how the dialog can be reused. This is not APEX specific; it is a common UI framework pattern but is often ignored in web apps. This means that if you are trying to take data entered in a dialog and insert or use it in the parent page, this should be done from event handlers or dynamic actions on the parent page not from code on the dialog page. You should not try to use JavaScript on the dialog page to try to manipulate the parent page.”

For example, using the Close Dialog Pager Process method (method “a”), you can set a comma separated list of items to return:


You may think “that’s all well and good in theory but I’m going to ignore this ideology and, in direct contravention of the above advice from John Synders, I’m going to use JavaScript on the dialog page to manipulate the parent page, just because it’s what I know and I’m not really bothered about reusing my dialog. I can just use something like parent.$(“#P100_SOME_ITEM”).val( ’some val from dialog’ ) to reference the parent and the job’s done.”

If you’re only using one level of dialogs (i.e. not a nested dialog) you can do this if you really want to and are happy to ignore the point about reusability of the dialog. However, If you are within a nested dialog (i.e. a dialog opened from another dialog) you’ll have problems with this since parent  will refer to the top level page and NOT the parent dialog. Search the John Synders article referenced above for the term “top level” to see why it’s built this way i.e. why all dialogs, nested or otherwise, are created in the parent page.

My advice would be NOT to do this in any case. Stick to the principle that the Dialog should not know about its caller.

How to respond to the closing of a dialog

So, great, now we have a means to close a dialog and specify which of the items in the dialog we wish to be available in the parent/calling window. The question then is: How do we get access to those values in the calling/parent window?

Again, we have a couple of options.

OPTION 1: Use Dynamic Actions

Create a Dynamic action with “Event Dialog Closed” as in the screenshot below. Note that the value for Selection Type is very important. If this is wrong, this will not work. This needs to be the item that triggered the opening of the dialog i.e. the Triggering Element.


Add one or more true actions to it of type “Set Value”, choosing a Set Type of Dialog Return Item. (In our example Page 35 is the parent page and page 38 is the dialog):


Option 2: Do it all manually with Javascript (who doesn’t like to have more control?)

For this option just create an On Page Load Dynamic Action with a Javascript Action as follows:



// the data parameter will be set to an object containing the values passed back from the closed dialog (“The Return Items”)

// e.g.:

// { P38_ITEM_ID: “1234”, P38_ITEM_NAME: “ACME WIDGET”, dialogPageId: 38 }

apex.item(“P35_ITEM_ID”).setValue( data.P38_SELECTED_ITEM_ID );

apex.item(“P35_ITEM_NAME”).setValue( data.P38_SELECTED_ITEM_NAME);



Again, note that the value of #openDialogIcon is very important. To repeat: This is the element that was indicated as the trigger for the opening of the dialog.


Related articles

Details of apex.navigation Javascript API

John Synders on Modal Dialogs in APEX 5

Dialog Templates in APEX 5 (Oracle Documentation)

Categories: Uncategorized

Setting and retrieving CLOB values in Apex

February 7, 2014 9 comments

Imagine you have a textarea in your app where you want your users to be able to type in large quantities of text, click a button to store this in the database and then later retrieve this large quantity of text and redisplay it in that textarea.

You can’t do this as you would with other page items (i.e. just submit the page and have the values stored in session state). Any value over 32kb in size won’t work. To get round this there is a technique which involves using an APEX Collection called CLOB_CONTENT. Essentially, you just need the two Javascript functions below to get this working. They are both asynchronous and can be passed a Javascript callback function to be executed once the CLOB value has been set or retrieved.

The Javascript function to save large value to the CLOB_CONTENT Apex Collection

function setApexCollectionClob (pBigValue, callback) {

var apexAjaxObj = new apex.ajax.clob (

function() {

var rs = p.readyState;

if (rs == 4) {


} else {

return false;






Example usage:

setApexCollectionClob ('Some large text value...', function(){alert('Data saved to Apex Collection!')})

The Javascript function to retrieve a large value from the CLOB_CONTENT Apex Collection

function getApexCollectionClob(callback) {

var apexAjaxObj = new apex.ajax.clob (

function() {

var rs = p.readyState;




return false;





Example usage:

getApexCollectionClob (function(pReturnedClobValue){ $('#P1_TEXTAREA').val(pReturnedClobValue) })

Retrieving the set value via PL/SQL in, for example, an Application Process

INTO l_clob
FROM apex_collection
WHERE collection_name = 'CLOB_CONTENT'

INSERT INTO my_table (id, myclob) values (123,l_clob);



Categories: Ajax, ApEx

Passing more than 10 values with apex.server.process

February 7, 2014 Leave a comment

You may be familiar with the apex.server.process function exposed by Apex’s Javascript API. It allows you to asynchronously interact with Apex Application Processes.

A simple example would be.

Apex Application Process

HTP.p('You passed "'||APEX_APPLICATION.g_x01 ||'" as the value for x01. ');

HTP.p('You passed "'||APEX_APPLICATION.g_x02 ||'" as the value for x02. ');

HTP.p('You passed "'||APEX_APPLICATION.g_x03 ||'" as the value for x03. ');


apex.server.process ( 
,   {   x01: 'my first custom value'
    ,   x02: 'mysecond custom value'
    ,   x03: 'my third custom value'
 , { dataType: 'text'
 ,success: function(pData){alert(pData)}

If you were to create the Application Process “MY_APP_PROCESS” and run the Javascript above, you’d see an alert popup:


You passed “my first custom value” as the value for x01.
You passed “mysecond custom value” as the value for x02.
You passed “my third custom value” as the value for x03.


You can use use x01 through to x10 to pass up to 10 parameters to your application process. What about if you want to pass more than 10 parameters, though? To do this, you first need to create a number of Application Items. You might like to call them :G_11, :G_12, :G_13 etc..

You can then set the values of these items in session state (and hence make them available in your Application Process) by doing the following:

apex.server.process ( 
,   {   x01: 'my first custom value'
    ,   x02: 'mysecond custom value'
    ,   x03: 'my third custom value'
    ,   p_arg_names: ['G_11','G_12','G_13']
    ,   p_arg_values: ['My 11th custom value','My 12th custom value','My 13th custom value']
 , {    dataType: 'text'
    ,   success: function(pData){alert(pData)}

Referencing these values inside your Application Process is simply a case of using Bind Variable syntax, e.g.:

HTP.p('You passed "'||APEX_APPLICATION.g_x01 ||'" as the value for x01. ');

HTP.p('You passed "'||APEX_APPLICATION.g_x02 ||'" as the value for x02. ');

HTP.p('You passed "'||APEX_APPLICATION.g_x03 ||'" as the value for x03. ');

HTP.p('You passed "'||:G_11 ||'" as the value for G_11. ');

Categories: Ajax, ApEx Tags:

Adobe Fireworks Colour Picker Problem on Mac OS X Mountain Lion

I’ve had a problem with Adobe Fireworks CS5 ever since upgrading to a Retina Display MacBook. The problem is that the eye-dropper colour picker tool just doesn’t work any more. At all. Very frustrating.

I came across a very simple workaround today which can be found here:



Categories: Uncategorized Tags:

Controlling Hide/Show Apex Regions Using Javascript

April 19, 2013 2 comments

Hide/Show regions are very useful in that they allow users to hide certain on-screen content when it’s not relevant for them (and show it again just as easily) simply by clicking the small arrow icon in the top left of the region.



But how can you programmatically do the equivalent of clicking the arrow icons? Perhaps, for example, when a user clicks a particular button on your page, you want all Show/Hide regions to be expanded. How can you do this?

One answer is the snippet of code below. In this code, “MY_REGION” is the static ID of the region for which we wish to programatically click the show/hide arrow icon.

// How to programatically click a Hide/Show region hide/show button
$('#MY_REGION .uRegionControl').click();

We can use the snippet below to work out the current status of a Hide/Show region i.e. whether it is currently expanded or collapsed. The below snippet will return a value or “none” if the region is collapsed.

// How to find out whether a hide/show region is currently shown or not
$('#MY_REGION div.uRegionHeading').next().css('display');

We can build on the above to create a function which expands, collapses or toggles the state of a Hide/Show Region.

// ********************************************************
// ** Function setStateOfHideShowRegion(pRegionStaticId,pDoWhat)(type,id)  
// ** Collapses, Expands of toggles any Hide Show region which has a static id
// ** defined by the string pRegionStaticId.
// ** Returns either "collapsed" or "expanded" to indicate the status of the
// ** region once this function has run.
// ** pDoWhat: either "expand", "collapse" or "toggle"
// ********************************************************
function setStateOfHideShowRegion(pRegionStaticId,pDoWhat) {
    var returnState;
    var currentState = 'expanded';
    doWhat = pDoWhat.toLowerCase();
    if ($('#'+pRegionStaticId+' div.uRegionHeading').next().css('display') == 'none') {
        currentState = 'collapsed';
    if (doWhat == 'toggle') {
        $('#'+pRegionStaticId+' .uRegionControl').click();
        returnState = (currentState=='expanded'?'collapsed':'expanded');
    else if (doWhat == 'expand') {
        if (currentState !== 'expanded') {
            $('#'+pRegionStaticId+' .uRegionControl').click();
        returnState = 'expanded';
    else if (doWhat == 'collapse') {
        if (currentState !== 'collapsed') {
            $('#'+pRegionStaticId+' .uRegionControl').click();
        returnState = 'collapsed';
    return returnState;
Categories: ApEx, Javascript, jQuery

Advanced Javascript Tutorial

Today I came across a great interactive tutorial which covers a series of advanced Javascript topics. It’s very well put together and lets you try out your own variations of the code being shown which can be very handy in making sure your understanding is correct.

It’s from John Resig, the creator of jQuery. The tutorial is not jQuery-centric though. In fact, it doesn’t talk about or use jQuery at all. It’s just pure Javascript.

You can find it here:


Categories: Uncategorized Tags:

Restoring a dropped table

ImageCatastrophe! You’ve just accidentally dropped a table which contained really rather important data. What to do?

One thing you can do to recover the situation quickly (if you’re running 10g or later, that is) is to run the following command:


If the table is still in the Recycle Bin, it’ll be recovered straight away. You can check whether the table is still available in the recycle bin and whether it can be recovered this way, with the following SELECT statement:

SQL> select original_name, can_undrop from recyclebin;

-------------------------------- ---

You can read about the Recyle Bin and Flashback Drop here:

Categories: SQL Tags:

An Interesting Feature of NOT IN and Multi-Row Subqueries

March 1, 2013 4 comments

Take the following simple SQL statement:

FROM dual
(SELECT 'a' FROM dual);

Since ‘x’ cannot be found in our subquery, you’d expect this to return a row from Dual right? Indeed it does:

  2  FROM dual
  3  WHERE 'x' NOT IN
  4  (SELECT 'a' FROM dual);


What about in the following case?

FROM dual
(SELECT 'a' FROM dual

You might expect this to return a row as well since ‘x’ is still not in our subquery (which now returns two values: ‘x’ and NULL).

You’d be wrong, however. This query will in fact return no rows:

  2  FROM dual
  3  WHERE 'x' NOT IN
  4  (SELECT 'a' FROM dual
  6   SELECT NULL FROM dual);

no rows selected

If the subquery referenced by your NOT IN statement contains any NULL values, the NOT IN condition will evaluate to unknown.

It’s worth noting that this is not the case if you use an IN as opposed to a NOT IN:

  2   FROM dual
  3   WHERE 'a' IN
  4   (SELECT 'a' FROM dual
  5    UNION ALL
  6    SELECT NULL FROM dual);

Categories: SQL Tags: ,

Preventing calls to console.log throwing errors in IE

October 11, 2012 Leave a comment

When developing applications which make a lot of use of Javascript, it can be very useful to use the console.log() function to output debug to Firefox’s Firebug console or to Chrome’s Javascript console.

However, such calls will cause errors when run inside IE as it does not by default have a console object (depending on the version of IE you are running and what add-ons you have installed). These errors may prevent other aspects of your Javascript from running, essentially breaking you application.

One quick solution to this is to add the following piece of jQuery to your code which will prevent console.log() from doing anything at all in IE and hence preventing the errors from occurring:



if ($.browser.msie) {console = { log: function() {} }};



Alternatively, you can incorporate the following piece of code which will, in IE, cause all messages sent to the console via console.log() to be alerted using alert() instead:


if ($.browser.msie) {console = { log: function(msg) {alert(‘Debug: ‘+msg);} }};



Categories: jQuery