Archive for June, 2008

Using Ajax to populate one field based on another in an ApEx form

Friday, June 20th, 2008

Often it can be useful to populate one field on a form based on the content of another in a live and dynamic fashion. Luckily this is easily achieved using the Javascript API supplied as part of ApEx 3.1.

This post illustrates an example where we wanted to lookup a value (called IMD Rank) based on the Postcode entered in another field. Users can either enter it manually, or click on a ‘Lookup’ button next to the field.

Firstly, define your process that will calculate the required value. This is most easily achieved by creating an Application Process (under Shared Components) which will run ‘on demand’.

This PL/SQL is just retrieving a value (RANKOFIMD) from a table based on a page item (P27_P9_POSTCODE). Note the ‘htp.prn()’ function call which will actually ’send’ the result to our page.

Next, define the JavaScript required to execute the process and return a value.  We’ll be displaying the returned value in the page item called P27_P16_IMDRANK. Add this script to the HTML Header section of your page.

Note : it is OK to append this script to any code which may already be in this field.

Next, add a button to invoke the JavaScript. I just added a standard HTML button after the element :

Clicking on the Lookup button now populates the field based on the value retrieved by the GetIMD application process.

Tags: , , ,
Posted in Oracle | No Comments »

Editting ApEx theme images using Mac OS X and WebDAV on XE

Friday, June 6th, 2008

Recently we had to make a couple of minor tweaks to a couple of ApEx theme images stored in an XE installation.  At first we scratched heads a little but then found a helpful post on the ApEx forum which made it much easier.  All we needed to do was work out how to do this using Mac OS X Leopard.  Luckily, it’s also very easy!

Firstly open a Finder winder then choose Go -> Connect to Server from the Finder Menu :

Connect to Server

Enter the server name and port that the embedded HTTP gateway is listening on (8080 by default) and click on Connect.  You will then need a database user name that has permission to modify the ApEx schema (FLOWS_*).  A DBA user name or the FLOWS_* user name and password should work fine :

WebDAV File System Authentication

With this done, click OK and logon to the database. A new Finder window will then appear showing the ‘root’ of the database WebDAV file system :

You can now navigate through the tree to find the image in question by following through i -> themes -> theme X :

WebDAV theme path

I haven’t yet worked out how to edit the images ‘in place’ but it’s easy enough to copy the required images out to a folder on your hard disk, edit them, and then copy them back into the appropriate folder.

Tags: , , , , , ,
Posted in Oracle | No Comments »

Change the background colour of an ApEx page

Wednesday, June 4th, 2008

The simplest way to change the background colour of an ApEx page is to edit the <body> template using the Application Builder.

Login to ApEx and navigate to your application.  Click on ‘Shared Components’, and then ‘Templates’ to view the templates for the current application theme.

ApEx templates

Navigate down to find the appropriate page template that you are using (tip: the ‘References’ column holds a count of the number of uses for a template, so if you’re not sure look for something greater than zero).  When you’ve found your template, click on it to edit the HTML elements of the template :

Edit template HTML header

Here you can see we’re using the ’style’ tag to set the background colour (using background-color) to the hex colour #46C6BA.  Standard HTML colour names such as ‘white’, ‘blue’ etc can also be used here.

Tags: , , ,
Posted in Oracle | No Comments »

New ApEx book published

Tuesday, June 3rd, 2008

We just spotted this new ApEx book on Amazon entitled “Pro Oracle Application Express” :

It’s not released yet but looks good, and is written by two giants of the ApEx scene, John Scott and Scott Spendolini, so we’ve placed our order already!

Tags: , ,
Posted in Oracle | No Comments »

Extracting XML information using PL/SQL

Monday, June 2nd, 2008

Since version 9, Oracle has had support for XML data built into the heart of the database.  Sometimes however, it is not immediately obvious how to get data from an XML string, particularly if, like me, it’s not something you do that often!

Here is a small code fragment which, given an XML ’string’ (xml_char) extracts either some attribute values or node text :

DECLARE
  xml_char VARCHAR2(1024);
  xml      xmltype;
  v_type   VARCHAR2(32);
  v_server VARCHAR2(32);
  v_port   VARCHAR2(32);
BEGIN
  -- create dummy XML document
  xml_char := '<?xml version="1.0"?>' ||
              '<CONNECTION>' ||
              '   <MAPSERVER type="Arcims">' ||
              '      <HOST>uranus</HOST>' ||
              '      <PORT>5300</PORT>' ||
              '   </MAPSERVER>' ||
              '</CONNECTION>';
  -- convert to XML from char type
  xml := xmltype.createxml(xml_char);
  -- extract some elements
  v_type   := xml.EXTRACT('/CONNECTION/MAPSERVER/@type').getstringval();
  v_server := xml.EXTRACT('/CONNECTION/MAPSERVER/HOST/text()').getstringval();
  v_port   := xml.EXTRACT('/CONNECTION/MAPSERVER/PORT/text()').getstringval();
  -- display them
  DBMS_OUTPUT.PUT_LINE('Type   = ' || v_type);
  DBMS_OUTPUT.PUT_LINE('Server = ' || v_server);
  DBMS_OUTPUT.PUT_LINE('Port   = ' || v_port);
END;

Firstly an XML ‘object’ is created from the xml_char XML string, we can then call the ‘EXTRACT’ method to get the relevant attribute or node value.

Tags: , , ,
Posted in Oracle | No Comments »