Archive for July, 2010

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