Author Archive

New Release of jQuery UI

October 9, 2012 Leave a comment

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:

Categories: Uncategorized

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

Using Dual to reduce function calls (and getting your 5-a-day)

February 7, 2011 1 comment

An introduction to an interesting feature of
Oracle SQL through the medium of fruit…



Make a FRUITS table.

CREATE TABLE fruits (fruit_name varchar2(30));

Put in 5 bananas, 7 apples and 3 blueberries.
INSERT INTO fruits VALUES ('banana');
INSERT INTO fruits VALUES ('banana');
INSERT INTO fruits VALUES ('banana');
INSERT INTO fruits VALUES ('banana');
INSERT INTO fruits VALUES ('banana');
INSERT INTO fruits VALUES ('apple');
INSERT INTO fruits VALUES ('apple');
INSERT INTO fruits VALUES ('apple');
INSERT INTO fruits VALUES ('apple');
INSERT INTO fruits VALUES ('apple');
INSERT INTO fruits VALUES ('apple');
INSERT INTO fruits VALUES ('apple');
INSERT INTO fruits VALUES ('blueberry');
INSERT INTO fruits VALUES ('blueberry');
INSERT INTO fruits VALUES ('blueberry');

Create a sequence for keeping track of the number of times our function below is called.

Make our function which returns a fruit’s colour (and increments our sequence each time it runs).
CREATE OR REPLACE FUNCTION get_colour (p_fruit_name IN varchar2)
RETURN varchar2
l_num number;
SELECT seq.nextval INTO l_num FROM dual;

CASE p_fruit_name
WHEN 'banana' THEN RETURN 'yellow';
WHEN 'apple' THEN RETURN 'green';
WHEN 'blueberry' THEN RETURN 'blue';
END get_colour;

Get the colour of each fruit in our table.
SELECT get_colour(fruit_name) FROM fruits;

QUESTION ONE: What will the following return?
SELECT seq.nextval FROM dual;

Well, there are 15 rows in our table so we’d expect the function to have run 15 times. And indeed it has: seq.nextval will show a value of 16.

Now, let’s reset the sequence for the next experiment.

And again get the colour of all of our fruits using our function but this time wrapping it inside a SELECT FROM dual.
SELECT (SELECT get_colour(fruit_name) FROM dual)
FROM fruits;

QUESTION TWO: What will the following return now?
SELECT seq.nextval FROM dual;

Well, we might be tempted to think that the function would have run 15 times as before and hence our sequence’s nextval would now be 16, as before. However, this is not the case. You’ll find the value returned is in fact 4, meaning that the function ran 3 times.

What happened there?
How can the function have run only 3 times if we gave it 15 bits of data to process (i.e. 15 fruits, one for each row from our table) and it returned the correct answer in each case?

The answer is Scalar Subquery Caching whereby the result of a “SELECT some_function(x) FROM dual” will be remembered for x as soon as it is run once. Hence, the function need only be run for each of the different fruits in the table and since we have only 3 different fruits (blueberry, apple and banana), the function is only run 3 times.

Tom Kyte talks about it here.

Nifty, eh?

Categories: SQL

ChangeCase: A Simple Apex 4.0 Plugin

July 16, 2010 2 comments

A foray into Plug-Ins

To educate myself in the ways of the new Apex 4.0 Plug-Ins feature, I decided to create a simple item Plug-In which automatically converts all the text entered into the item either to uppercase or to lowercase, depending on which option the developer chooses. I’m making it available here not so much because I think it will be a useful Plug-In (though perhaps it might be if your requirement is very simple) but rather in the hope that it might be helpful to anyone else looking at Plug-Ins for the first time. I have heavily commented the code to explain what each bit is doing.

See it in action

A working example of this plugin can be seen here.

Download and install

The plugin itself can be downloaded here.

Once you have downloaded the Plug-In, you can install it by going to Shared Components > Plug-Ins > Import and following the prompt. You will then be able to create ChangeCase items in your application, choosing whether you want the contents to be automatically uppercased or lowercased.

Creating it from scratch

If, for the hell of it, you want to create this Plug-In from scratch as opposed to just downloading and installing it you can do so by following these steps.

  1. Within Application Builder go to Shared Components > Plug-ins and click “Create >”.
  2. Specify the following details and then click “Create”:

    : ChangeCase
    Internal Name: (or change this to be a universally unique name based on your domain name)
    Type: Item
    PL/SQL Code: Paste in the contents of this file. This contains two PLSQL functions (render_changecase and validate_changecase which we reference in the Render Function Name and Validation Function Name properties of this Plug-In). This PL/SQL code is heavily commented so should hopefully be fairly self-explanatory.
    Render Function Name: render_changecase
    Validation Function Name: validate_changecase
    Standard Attributes: Tick the checkbox labelled “Is Visible Widget”. If you do not tick this checkbox then there will be no option to specify a Label for our ChangeCase Item Plugin when we create an instance of it in our application. Since this is essentially just a slightly-modified Text Field, we certainly want the ability to specify a label.
  3. Click “Custom Attributes”. When we create a new instance of our new Plug-In, we want to be able to decide whether we want the contents to be automatically uppercased or lowercased so we create a custom attribute here to allow that decision to be made.
  4. Click “Add Attribute”.
  5. Specify the following details and then click “Create”.

    : Component
    Attribute: 1
    Label: Case Alteration Option
    Type: Select List
    Required: Yes
    Default Value: UPPER
    Add Value button in List of Values Section: Create two entries. One with a Display Value of “Uppercase” & a Return Value of “UPPER”; and the other with a Display Value of “Lowercase” and a Return Value of “LOWER”.
  6. We want the value entered into any instance of our new Plug-In Item to be either automatically uppercased or lowercased. We want this to happen client-side so for this we need some Javascript. That very simple Javascript can be found in this file. Download this file to your computer.
  7. Back in Application Builder, click “Files” and then the “Upload New File” button.
  8. Browse for the changecase.js file that you just downloaded and then click “Upload”.
  9. Finally, click “Apply Changes”.

You will now be able to create ChangeCase items in your pages where you’ll be able to specify, under settings, whether you want the content automatically uppercased or lowercased.

Categories: Uncategorized

Free Full UK Postcode Data

Ordnance Survey Free Postcode DataFor a long time the full set of UK Postcode data has been subject to quite a hefty fee. I didn’t realise until today but, earlier this year, this information was made available by Ordnance Survey free of charge.

The data can be downloaded from here (

Just tick the checkbox entitled Code-Point Open then click Next. They then email you a link to the CSV file to download (about 20 mb).

Now, can this data be incorporated into a nifty Apex 4 Plug-in?

The License

The licensing for this data seems pretty open, too, which is good news.

Taken from the Ordnance Survey License found here:

You are free to:
 copy, distribute and transmit the Data;
 adapt the Data;
 exploit the Data commercially whether by sub-licensing it, combining it with other data, or by including it in your own product or application.

A Note on the CSV File

In the CSV files that you will download from the link that Ordnance Survey send you, column K is the British National Grid (BNG) X co-ordinate and column L is the BNG Y co-ordinate. You can use for converting the odd co-ordinate backwards and forwards between BNG and Lat/Lon.
Categories: Uncategorized

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

New URL for this blog

March 26, 2009 2 comments

I recently downloaded and installed the really rather brilliant open source blogging software from I recommend having a play about with it if you have a web server with PHP and MySQL available to you. This will be the last entry to The new home for this blog is:

Categories: Uncategorized

Replacing text in a file using Perl

I’ve been working with Windows Batch Scripts recently. The aim has been to create a set of scripts which will automate the installation and configuration of certain applications. I was taking in user input using the SET /P MYVARIABLE=Please enter a value: syntax at the beginning of the batch script and needed to replace existing text in a configuration file with whatever the user entered at the prompt.

You can achieve this, after a fashion, using batch scripts but it’s a bit of a pain. There is a much better, easier and quicker way to do exactly the same thing using Perl. Chances are you will already have a copy of Perl available if you have the Oracle Database installed. It can all be achieved from one line typed directly into the command prompt (no need even to create a Perl script!). Example below.

The file before

The Perl command

This will replace all instances of the string “banana” in the file test.txt with the string “apple”. The search will be case insensitive. If perl.exe is not already in your PATH, you will need to call it by explicitly specifying the path where it is located or by first adding the directory where it can be found to your PATH environment variable.

The file afterwards

Categories: Batch Scripting, Perl

SQL: SELECTing a random subset of data

February 6, 2009 2 comments

Show me some data, Baby

Have a table with a lot of data in and want to work with a random subset of that data? The answer is the Sample Clause of the SELECT statement. The syntax is often described as something like the following:

FROM my_table SAMPLE (percentage_of_rows);

For example:


Table created.





"Some data" is not always the same "some data"

The above is demonstrably returning a random 10% of data from the ANDY.objs table. Great. Very useful. However, an (arguably) interesting thing happens if we run the exact same SELECT statement again:



And again:



We get a different number of rows each time. It’s still close enough to 10% of the data, you might say. Maybe so but it does highlight an interesting point: The Sample Clause does not bring back x% of rows (as is often thought) but rather gives each row an x% chance of being returned.

From the Oracle Documentation:
"This percentage indicates the probability of each row, or each cluster of rows in the case of block sampling, being selected as part of the sample. It does not mean that the database will retrieve exactly sample_percent of the rows of table."

The moral of this tale

Use the Sample Clause to work with a random subset of data but don’t rely on it to bring back exactly the expected number of rows. Complete details on the Sample Clause can be found here and more examples can be found here.

Categories: SQL