Andrew Tulley

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:

APP_BUILDER_SESSION

e.g. v(‘APP_BUILDER_SESSION’)

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

Related documentation: https://docs.oracle.com/database/apex-18.1/HTMDB/understanding-substitution-strings.htm#GUID-62FE6E65-265A-4BE4-B04B-F90BDA317328

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.

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.

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”.
4. When you try to save the the new Page Template you may see errors such as the following:To rectify these, locate the three fields mentioned and paste in the default values shown for each, as shown below.
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.
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.

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

THE SIMPLE WAY THAT YOU DON’T HAVE MUCH CONTROL OVER BUT WILL BE PERFECTLY SUFFICIENT AND QUICKER TO PUT IN PLACE IN MOST CASES

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.

THE MANUAL WAY THAT YOU HAVE MORE CONTROL OVER BUT TAKES A BIT LONGER

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_url=>’f?p=400:38:&SESSION.::NO:RP::’
, 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>’ );

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

$(“#openDialogIcon”).on(“apexafterclosedialog”,function(e,data){

// 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

https://docs.oracle.com/cd/E59726_01/doc.50/e39149/javascript_api.htm#AEAPI30096

John Synders on Modal Dialogs in APEX 5

http://hardlikesoftware.com/weblog/2015/05/22/apex-5-0-dialogs/

Dialog Templates in APEX 5 (Oracle Documentation)

http://docs.oracle.com/cd/E59726_01/doc.50/e39147/bldapp_pg.htm#HTMDB30099

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) {

callback();

} else {

return false;

};

}

);

apexAjaxObj._set(pBigValue);

}

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;

if(rs==4){

callback(p.responseText);

}else{

return false;

}

}

);

apexAjaxObj._get();
}

Example usage:

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

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

DECLARE
L_CLOB CLOB
BEGIN
SELECT CLOB001
INTO l_clob
FROM apex_collection
WHERE collection_name = 'CLOB_CONTENT'

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

HTP.p('SUCCESS');

END;

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. ');

Javascript

apex.server.process ( 
  "MY_APP_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 ( 
  "MY_APP_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. ');

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:

http://simianstudios.com/blog/post/colour-picker-bug-workaround-for-adobe-fireworks-cs4-in-os-x-lion

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;
    
}

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: http://ejohn.org/apps/learn/

Catastrophe! 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:

FLASHBACK TABLE MY_SCHEMA.MY_SUPER_IMPORTANT_TABLE TO BEFORE DROP;

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;

ORIGINAL_NAME CAN
-------------------------------- ---
MY_SUPER_IMPORTANT_TABLE YES

You can read about the Recyle Bin and Flashback Drop here: http://docs.oracle.com/cd/B19306_01/backup.102/b14192/flashptr004.htm

Take the following simple SQL statement:

SELECT *
FROM dual
WHERE 'x' NOT IN
(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:

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

D
-
X

What about in the following case?

SELECT *
FROM dual
WHERE 'x' NOT IN
(SELECT 'a' FROM dual
 UNION ALL
 SELECT NULL 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:

SQL> SELECT *
  2  FROM dual
  3  WHERE 'x' NOT IN
  4  (SELECT 'a' FROM dual
  5   UNION ALL
  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:

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

D
-
X

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:

$(document).ready(function(){

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:

$(document).ready(function(){

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

})

I’ve just noticed that there’s a new version of jQuery UI: 1.9. It includes nifty new widgets including Menu, Spinner and Tooltip. And who doesn’t love new widgets?

You can read about it and see demoes of the new functionality here: http://blog.jqueryui.com/2012/10/jquery-ui-1-9-0/

As far as I’m aware, there’s no out-of-the-box call made available by Apex to allow you to click a particular button in a Region Selector programmatically via Javascript.

The following bit of jQuery will allow you to do this. Just change the string “Personal Bio” to be whatever the string is that appears in the Region Selector button you want to programatically “click”:

$(‘span:contains(“Personal Bio”)’).parent().click();

Be aware that the string (“Personal Bio” in the above example) is case sensitive.

With a bit more work this technique could be employed so that your pages “remember” which Region was last selected in the Region Selector whenever you go back to a page.