Archive for September, 2007

ApEx: Ensuring a button is clicked only once

September 16, 2007 6 comments



Click here to view the effect. You will notice that as soon as you click either of the buttons, the button is greyed out and its text is changed to “Submitted…”


Create a button of type “Create a button in a region position”.


Set the button’s target to be a URL.



If you do not want a confirm dialog with the button, set the button’s URL target to the following (Note: The below has been put on 3 lines for ease of viewing but should be entered into the URL target all as one line of text):


If you DO want a confirm dialog with the button, set the button’s URL target to the following (Note: The below has been put on 4 lines for ease of viewing but should be entered into the URL target all as one line of text):

javascript:if (confirm('Are you sure you want to?')) {
this.disabled=true; this.value='Submitted...';

In either case you should change the value of XXXXXX to be the same as the name of the button. This is the value that REQUEST will be set to when the button is clicked. By setting it to be the same as the name of the button it means that you can still use “When Button Pressed” conditions for this button.

Categories: ApEx

ApEx: Client-side date auto-formatting

September 16, 2007 8 comments

Firstly, all credit to a chap called Matt Kruse who created the superb Javascript functions referenced in this blog entry. His site can be found here.


To see the effect, go here and type some dates into the datepicker field (type as opposed to select from the date picker itself). You can type them in pretty much any format you like (e.g. mm/dd/yyyy, mm/dd/yy, mm-dd-yyyy, mm-mon-yy). Then click out of the field (or cause it to lose focus via some other means). You will notice that the date is automatically reformatted to be of the form dd-mon-yyyy. If you enter a date in a format that is not recognised, an alert appears telling you so.

NOTE: The example deals with dates in the US format. See the note later in this entry for details on dealing with other formats.


You will need to get yourself a copy of Matt Kruse’s very useful date functions. These are available from this page of his website.

You will need to include these functions in your ApEx application via some means or other so that they can be called from any page, e.g.

* Paste them into a region on page zero.
* Upload them as a .js file and reference the file in your page headers (or include reference to the .js file in your page template).


Create an Application Item called APP_FORMAT_DATE_HTML


Create an Application Computation of type “On New Instance” for the application item APP_FORMAT_DATE_HTML, setting its value to the text below:

onBlur="var d=parseDate(this.value);if(d==null&&this.value!=''){alert('This is not a valid date.');} else if(this.value=='') {} else {this.value=formatDate(d,'dd-NNN-yyyy').toUpperCase();}"


For every item in your application (normally Date Picker items but will work with plain text items) that you want to Auto Format, enter the following into the HTML Form Element Attributes:


And that’s it.


* You might want to have the date be autoformatted to some other format than dd-mon-yyyy. This can be achieved by changing the line in the APP_FORMAT_DATE_HTML computation which reads:


to something else such as:

this.value=formatDate(d,’MMM d, y’);

For a complete list of the valid date formats you can use, please look at Matt Kruse’s source comments here.

* To have the script check for British format dates before US format dates (i.e. dd/mm/yyyy as opposed to mm/dd/yyyy) simply amend the line in the APP_FORMAT_DATE_HTML computation which reads:




Categories: ApEx, Javascript

ApEx: Search facility on every page

September 15, 2007 1 comment

Back in April I blogged about using the BRANCH_TO_PAGE_ACCEPT request value. This entry talks about how you can use this request value (amongst other things) to implement a search on every page of your application.

The below makes reference to just one very simple table which contains details of CDs.

Table definition:
CREATE TABLE cds ( id NUMBER , album_name VARCHAR2(200) , artist_name VARCHAR2(200));


Enter CD ID
To see this effect in action click here. The bit we’re interested in is the text box on the left of the screen labeled “Enter CD ID”. Type the ID of a CD held in the database (only IDs 1 to 7 exist at the moment) and click “Search” (or press ENTER) and you will be taken directly to the details for that CD. The buttons labeled “Go to Example Page A” and “Go to Example Page B” will take you to pretty much entirely empty pages in the application. These pages are there just to highlight the fact that the “Enter CD ID” textbox is available on every page of the application.

STEP 1: Creating the Search Page (page 3)
Note: This and all the other pages should be of type “No tabs with Sidebar”.

The Search Page
On this page there’s one text item labeled “Enter Album ID” with a name of “P3_SEARCH”, a SEARCH button and a hidden item called P3_STATUS. On clicking the SEARCH button a process of type PL/SQL is fired, whose source is:
FROM cds
WHEN no_data_found THEN

All this does is:- If a row can be found in the CDS table with the ID that was entered into P3_SEARCH field then P3_STATUS is set to ‘FOUND’, otherwise it is set to ‘NOT_FOUND’.

There are also 2 branches on this page.

Branch 1: The first branch takes us to the “View CD Details” (page 2) and sets the item P2_ID on that page to have the same value as that which was entered into P3_SEARCH. This branch is only fired if P3_STATUS = ‘FOUND’ (i.e. if there is a CD in the table with the ID that was entered into the search field.)

Branch 2: This second branch takes us back to the Search Page (page 3) and fires only if P3_STATUS= ‘NOT_FOUND’ (i.e. if there is not a CD in the table with the ID that was entered into the search field.)


STEP 2: Creating the View Details Page (page 2)

This is the page which shows details of specific CDs held in the database. 4.jpg
The important part of this page is the Before Header PLSQL process which populates the items on the page which details of the CD we are interested in:
SELECT album_name , artist_name
FROM cds
WHERE id = :P2_ID;

Step 3: Page Zero

With pages 2 and 3 built there is essentially a fully functioning (although very simple) search facility: You can type in the ID of a CD and get its details. Now we need to make this search functionality accessible from every page of the application. The following achieves this: An HTML region has been added to page Zero in “Region Position 02″ with a template of “No Template” and a source of the following:

<script language="javascript">
function doSearch() { window.location='f?p=&APP_ID.:3:&SESSION.'
+document.getElementById('sidebar_search').value; }

function enterPressed(e){
var keycode;
if (window.event) {keycode = window.event.keyCode;}
else if (e) {keycode = e.which;}
else {return true;}

if (keycode == 13){ doSearch(); return false; }
else { return true; }

<span style="width:90px;" align="center"> Enter CD ID </span>
<input type="text" id="sidebar_search" style="width:90px;"
onkeypress="return enterPressed(event);"
<input type = "button" value="Search" style="width:90px;" onclick="doSearch()">

You’ll notice that the javascript function “doSearch” redirects to page 3 (the search page) setting the REQUEST value to “BRANCH_TO_PAGE_ACCEPT|SEARCH”. Going to the URL specified in the doSearch function is exactly the same as having submitted the search form on page 3 itself (in virtue of the presence of BRANCH_TO_PAGE_ACCEPT in the REQUEST value). It is this which allows the search functionality in the sidebar to work on every page.

The “enterPressed” javascript function ensures that pressing the ENTER key when focussed on the search field does exactly the same as clicking the search button.


This is a very simple example. You might, for example, want to:

* Make the search more flexible than just searching for an exact ID (using LIKE or Oracle Text etc.) You could then redirect directly to the record in question if only one hit is returned or to a list of Search Results if more then one record is returned. See blog entry here for a method of achieving this.

* Only show the Search Box on certain pages of your application. To achieve this you could add a condition of type “Current page is contained within expression 1” to the No Template HTML region on page Zero

* Make it prettier :)


You can download the application which demonstrates this from here.

Categories: ApEx

SQLInForm: SQL / PLSQL Formatter

September 12, 2007 2 comments


I spotted a link to this site over at Laurent Schneider’s always-informative blog.

It’s a web-based Java Applet called SQLInForm for formatting PLSQL and SQL code. It gives you plenty of options including how you would like the code to be indented, aligned etc.

Of course, SQL Developer will do much the same thing for you but requires a download.

Anyway, SQLInForm can be found here.

Categories: PL/SQL