Archive for the ‘ApEx’ Category

Setting and retrieving CLOB values in Apex

February 7, 2014 8 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:

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

Clicking a Region Selector Button Programmatically

October 9, 2012 Leave a comment

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.

Categories: ApEx, jQuery

Converting strings into nonsense



Most of the time you want to make sense.

Sometimes, however, you want to make nonsense out of sense.

What gibberish is this? Good question.

The following script allows you to convert any string that you pass it into a string of exactly the same form but where each word has been replaced with another random word of the same length. Punctuation and carriage returns are retained.


Why would this be useful?

If you have some data, perhaps stored in a table, which is sensitive in some way. You might want to use that data on an insecure environment for testing or some other purpose. You can’t put the data on there directly because of security concerns. So one solution would be to alter the data so that it was no longer sensitive but yet retained the same fundamental characteristics (in terms of word length etc.) as the original data.

The script

The script makes use of the wwv_flow_dictionary$ table in your Apex schema so you’ll need to make sure the schema you’re creating this function in has SELECT rights on that table. You’ll also need to  change “APEX_030200” below if you’re using a different version of Apex.

CREATE OR REPLACE FUNCTION mask_string (p_input VARCHAR2) RETURN VARCHAR2 IS l_output VARCHAR2 (4000); y NUMBER := 1; FUNCTION get_word_of_length (p_word_length IN NUMBER) RETURN VARCHAR2 IS l_word_output VARCHAR2 (1000); BEGIN SELECT words INTO l_word_output FROM (SELECT words FROM apex_030200.wwv_flow_dictionary$ WHERE LENGTH (words) = p_word_length ORDER BY DBMS_RANDOM.VALUE ()) WHERE ROWNUM = 1; RETURN l_word_output; END get_word_of_length; BEGIN DBMS_RANDOM.SEED (TO_NUMBER (TO_CHAR (SYSDATE, 'ddss'))); l_output := '~' || REPLACE (p_input, ' ', '~~') || '~'; FOR x IN 1 .. 22 LOOP y := 1; LOOP EXIT WHEN REGEXP_INSTR ( l_output, '[~\.,;!\?"''-:[:cntrl:]][^~\.,;!\?"''-:[:cntrl:]]{' || x || '}[~\.,;!\?"''-:[:cntrl:]]', 1, y ) = 0; l_output := REGEXP_REPLACE ( l_output, '([~\.,;!\?"''-:[:cntrl:]])[^~\.,;!\?"''-:[:cntrl:]]{' || x || '}([~\.,;!\?"''-:[:cntrl:]])', '\1' || get_word_of_length (x) || '\2', 1, y ); y := y + 1; END LOOP; END LOOP; l_output := REPLACE (l_output, '~~', ' '); l_output := SUBSTR (l_output, 2, 99999); l_output := SUBSTR (l_output, 1, LENGTH (l_output) - 1); RETURN l_output; END mask_string; /

SELECT mask_string(q'!
To be or not to be that is the question:
Whether 'tis nobler in the mind to suffer
The slings and arrows of outrageous fortune,
Or to take arms against a sea of troubles
And, by opposing, end them. To die, to sleep
No more  and by a sleep to say we end
The heartache and the thousand natural shocks
That flesh is heir to  ëtis a consummation
Devoutly to be wished!') from dual;

oh ad ma hoc pi Al toad OK sod realists:
tinning 'how topple vi nix ruin to gritty
cam gang's Vic arrows as angiosperm statues,
OK ad pals swab brassed a cow ex superior
jaw, nm treadled, Amy keel. on jut, us repay
go dyed  MBA Io x purer DB hew nd cue
The cannister vis lab Broadway snuffle shrove
Liss funny Io hone ix  Vega a ungenerously
capstans ax be surfer

Categories: ApEx, PL/SQL

Apex and SSO blank white screen problem: SOLVED

June 18, 2010 1 comment

Apex and SSO: The White Screen Problem

Unicorn MeatYou may have configured Apex to use Oracle Single Sign On (SSO) as your authentication scheme using instructions such as those found here. If you have, you may have noticed that if you leave the login screen (i.e. login.jsp) sat idly displayed in your browser for more than a certain amount of time (I’ve not done precise timings but it seems to be something like 5 minutes) and then try to login, when you click the Login button you’re presented with a blank white screen and nothing more.

Below, I offer a solution to this problem.

Why it happens

Before we talk about how to fix this, let me describe what I think the problem is here…

When you first request access to your Apex application which has SSO set as its authentication scheme, Apex generates a brand new shiny Apex session id for you and then redirects you to the SSO login screen (login.jsp be default but you may have modified this to be your own custom jsp) for you to enter your credentials. This jsp contains a hidden item with a name of site2pstoretoken (a name mandated by SSO). The value of this hidden item is a long string of seemingly random characters (V1.2~75D127345~6a5DDF88CFDCE765FBEF…). This string is in fact an encoded representation of various bits of data. The important bit of data for us which is encoded in this long string is the URL that SSO should forward us to when we have clicked the Login button and been successfully authenticated by SSO. The actual URL that is encoded in this string which SSO will try to forward us to is:


Where server and pls/apex and 100 and 10 are replaced by the relevant server name, DAD, application number and page number respectively. The crucial bit is the session id at the end (12345678 in the example). This will be replaced by the brand new shiny Apex session ID that Apex assigned to us. How did this URL get encoded into site2pstoretoken? It was passed when Apex originally redirected us to SSO.

So site2pstoretoken contains a URL to forward to. So what?

Good question. It seems that the brand new shiny Apex session ID which was originally assigned to us has the ability to “time out”. Once it has “timed out”, if you click Login on the login.jsp page, SSO will try to redirect you to f?p=100:10:12345678 (*). Since the session has “timed out”, Apex shows you just a blank white screen.

(*) Note: To be precise, the login.jsp submits to /sso/auth which in turn redirects you to /pls/apex/wwv_flow_custom_auth_sso.process_success. In the redirection to wwv_flow_custom_auth_sso.process_success, the site2pstoretoken is included in the url in the “urlc” parameter (e.g. /pls/apex/wwv_flow_custom_auth_sso.process_success?urlc=V1.2~75D127345~6a5DDF88CFDCE765FBEF…). It is this process_success procedure which appears to display the blank white screen. For info: the wwv_flow_custom_auth_sso is created when you ran custom_auth_sso.sql (see step 5 in the instructions for setting up Apex with SSO). I am not sure exactly what the process_success procedure is checking to cause it to display a white screen (the code is wrapped so can’t be inspected). The only thing I can hazard a guess at is that it looks in the <Apex schema>.wwv_flow_session$ table and if the ON_NEW_INSTANCE_FIRED_FOR column has not been populated within a certain amount of time since the session was created (CREATED_ON column) then it shows a white screen. (The ON_NEW_INSTANCE_FIRED column I’ve noticed is only populated once you actually access a page of your application. Just being redirected to SSO does not populate it.)

Anyway, how do we fix it?

So even though we can’t tell exactly the reason why wwv_flow_custom_auth_sso.process_success displays a white screen if you attempt to login after the login screen has been idle for some time, we can put in place a workaround to stop this happening. If we were to somehow refresh the site2pstoretoken on the login JSP periodically so that the value it held always represented (in an encoded form) a URL to redirect to which contained a valid, non timed-out Apex session then the problem should disappear. This can be achieved with a bit of Ajax in the login.jsp as follows. The javascript is all contained in +”…” syntax because it’s all part of one long out.println() call in your JSP. Also notice that I’ve wrapped 3 of the longer lines (the ones that don’t begin with a + below) just to fit into the format of this blog. You’d need to remove these 3 extra line breaks in your jsp :

+"function getXmlHttpRequestObject() {\n"
+"if (window.XMLHttpRequest) {return new XMLHttpRequest();} \n" 
+"else if(window.ActiveXObject) {return new ActiveXObject('Microsoft.XMLHTTP');} \n" 
+"else { alert('Your browser does not seem to support XMLHTTP.');} \n"

+"function getToken() { \n"
+"if (receiveReq.readyState == 4 || receiveReq.readyState == 0) { \n"
+"'GET','http://myserver:7777/gentoken/f?p=234:10',true); \n"
+"receiveReq.onreadystatechange = handleGetToken; \n"

+"function handleGetToken() { \n"
+"if (receiveReq.readyState == 4) { \n"
+"processedResponse = receiveReq.responseText;\n"
+"processedResponse = processedResponse.substring(
   processedResponse.indexOf('VALUE=\"')+7); \n"
+"processedResponse = processedResponse.substring(
   0,processedResponse.indexOf('\"')); \n"
+"document.getElementsByName('site2pstoretoken').item(0).value =
   processedResponse; \n"
+" \n"
+"var receiveReq = getXmlHttpRequestObject(); \n"

You’ll notice above that the Ajax / XMLHTTP request is made to http://myserver:7777/gentoken/f?p=100:10

In this example configuration, the Infrastructure tier (where SSO is installed) is accessed on port 7777. This is the same port on which you will be accessing login.jsp. The apex application is served using MODPLSQL from a different instance of Apache on port 80. So, really, we would need to make a call to server:80/pls/apex/f?p=100:10 but the problem is that browsers do not permit you to make cross-domain Ajax / XMLHTTP requests. Hence, we make the request to the same domain as we are currently on ( and add a proxy entry into the Infrastructure’s httpd.conf file to automatically forward any requests to server:7777/gentoken/ to server:80/pls/apex/. Whether you need to do this kind of configuration of httpd.conf will depend on your setup but for completeness the kind of entry you would need to add to your Infrastructure’s httpd.conf would be as follows:

<IfModule mod_proxy.c>
ProxyRequests On
ProxyPass /gentoken/ http://server:80/pls/apex/
ProxyPassReverse /gentoken/ http://server:80/pls/apex/

What is all that Javascript doing?

It is essentially requesting access to our Apex application again. Again, Apex realises the application is protected by SSO so assigns us a shiny new Apex session ID and forwards us to SSO. Or rather, it can’t actually forward us in this case because all this is is an XMLHTTP request, but it does still return HTML which, if we were a browser and not an XMLHTTP request, would cause us to redirect to SSO. Our Javascript scrapes the new site2pstoretoken which is contained in this HTML and assigns it to the value of the hidden site2pstoretoken already contained in our login.jsp. It does this every 60 seconds (well under the time period in which the Apex session ids seem to expire). Hence, whenever you click Login on your JSP, whether you’ve had it on screen for 30 seconds or 30 days, you will still be submitting a valid encoded Apex URL and will see your app and not just a blank white screen.

Categories: Ajax, ApEx

Apex: Speeding things up with Oracle Web Cache compression

February 6, 2009 1 comment

Apex pages on a diet

Duncan Mein last year wrote about enabling content compression using Oracle Web Cache to speed up use of both Apex apps and Oracle Discoverer.

Enabling compression in this way is a risk-free, quick win and there’s really no reason not to do it.

Feeling the burn

To enable compression for your Apex apps (including Application Builder itself), do the following:

  1. Access the URL (this is the default port for accessing Web Cache administration).
  2. Click "Caching, Personalization, and Compression Rules".
  3. Under "For all sites" select the last radio button and click "Insert Below".
  4. Add a rule for Get and Get with Querystring of type regular expression.
  5. Add the regular expression of: /pls/apex/.*$
    NOTE: You need to replace /pls/apex/ with the DAD (Database Access Descriptor) that you use to access Apex.
  6. You will be able to check that these settings have taken effect by taking a look at the HTTP response headers that your browser receives when you access one of your Apex pages.

    The above screenshot is from a tool called Live HTTP Headers available for Firefox. It’s an extremely useful tool, showing you exactly what is being set in  both the Request and Response headers. It can be installed from here. If you start playing with Live HTTP Headers, you may notice that in every request your browser sends to the server, it includes the following:

    This indicates to the server that your browser does indeed support gzip compression (which is what you have just configured within Oracle Web Cache). If your browser did not send the Accept-Encoding header as above, then the server would not send back compressed content but rather just plain old uncompressed content which would still be displayed correctly.

    You can see more general information about HTTP Request headers here and about HTTP Response headers here.

Taking the pills

The configuration that you performed above via Oracle Web Cache Administration is just a front-end for modification to the file webcache.xml found in:

<Oracle Middle Tier Home>\webcache\webcache.xml

As such, it is possible to perform these steps via script. This may be useful if, for example, you are trying to create a silent method for installation or configuration of your application. If you are using Windows, you can use the use the batch script and accompanying text file below to perform this configuration entirely via the command prompt (you could work this into a larger installation / configuration script depending upon your requirement).

enable_web_cache_compression.bat – You will need to modify the first line of this file to point to your Oracle Middle Tier Home.

new_webcache_entry.txt – You will only need to modify this file if your DAD is something other than /pls/apex/

Once you have saved these files to any directory and made the changes to these files as mentioned above you can just run enable_web_cache_compression from a Command Prompt to perform the relevant configuration.

DISCLAIMER: You should only use these files as a guide to one possible way for configuring Web Cache to use compression. You should only consider using them once you have looked at their contents and understand what they do. And, as always, you should have a backup of everything before you start modifying configuration files.

Categories: ApEx