Let's talk about Application Express

UKOUG TECHEBS 2011 Presentations

2011-12-08T14:12:00.002Z

I just wanted to do another quick, post-conference follow up post after UKOUG TECHEBS 2011 in Birmingham. At this conference I presented on 2 topics relating to Application Express and as promised, here are the slides and samples I showed during the sessions:

With the accessibility sample, by just looking at the application, I appreciate it's not easy to work out exactly what I did to make it more accessible, so will try and follow up with some more information in the next couple of weeks about the changes. (Hopefully the slides and application together are still of some use now, until I do this.) Also, I had some interesting feedback after the session, where 2 people suggested the screen reader demo's could be done with the projector switched off, which I thought was a great idea to try and provide a more accurate user experience, so will try and incorporate that next time.

Thanks to all who attended, I hope you got something from them.

Oracle OpenWorld 2011 Dynamic Action Presentation

2011-10-06T01:28:00.001+01:00

Just a quick post to follow up from my presentation today at OpenWorld, 'Oracle Application Express 4.1 - Dynamic Actions'. In the session, I promised to provide the slides and sample applications I used, so here they are.

The zip file contains the slides and 2 sample applications entitled 'Examples' and 'Common Questions'.

Thanks to all who attended, hope you found it useful!

Application Express 4.1 - #BUTTON_ID# Changed Behaviour

2011-07-21T13:28:00.012+01:00

I just wanted to blog about some changed behaviour that will be landing in APEX 4.1. The change has to do with the #BUTTON_ID# substitution string, available for use within a button template and we hope will have minimal impact.

What's Changing?

Prior to 4.1, the value substituted for the #BUTTON_ID# substitution string in a button template depended on the type of button:

Region buttons substituted the internal numeric ID of the button, for example '123456789101112'.
Item buttons actually failed to substitute this value at all, so you would just get the '#BUTTON_ID#' text.

We had to address and improve this for the new Dynamic Action / Button integration in 4.1, which means that the value substituted for #BUTTON_ID# will now be one of the following:

If the template is used by an item button, the item name is used, for example 'P1_GO'.
If the template is used by a region button and a 'Static ID' (new in 4.1) is defined for the button, this is used, for example 'my_custom_id'.
If the template is used by a region button and no 'Static ID' is defined, an ID in the format 'B||[Internal Button ID] will be used, for example 'B123456789101112'.

The change in behaviour that could be of potential significance in your applications is #3 above. This was changed so as to be a valid HTML 4.01 identifier (begins with a letter) and to be consistent with how we handle other component IDs in Application Express.

Will this impact your applications?

If you used a button template, that used an ID value substituted by #BUTTON_ID# (you would have had to add it to the button template, as this was never included by default in our themes), and importantly you hard-coded that ID from other places in your code (for example in custom JavaScript to attach behaviour to the button), then this change in behaviour will cause that code to no longer work.

What can you do about this?

To help you identify region buttons that use a template containing the #BUTTON_ID# substitution string, you can run the following query in your workspace in Application Express 4.0:

select aapb.application_id,
aapb.page_id,
aapb.button_name,
aapb.label,
aapb.button_id,
aapb.button_template,
aatb.template
from apex_application_page_buttons aapb,
apex_applications aa,
apex_application_temp_button aatb
where aapb.application_id = aa.application_id
and aa.application_id = aatb.application_id
and aa.theme_number = aatb.theme_number
and aatb.template_name = aapb.button_template
and aapb.application_id = [Your App ID]
and aapb.button_template is not null
and aapb.button_position = 'Region Position'
and upper(aatb.template) like '%#BUTTON_ID#%'
order by 1,2,3

Or when APEX 4.1 comes out, you'll be able to do this (by virtue of the addition of the BUTTON_TEMPLATE_ID column to the APEX_APPLICATION_PAGE_BUTTONS dictionary view):

select aapb.application_id,
aapb.page_id,
aapb.button_name,
aapb.label,
aapb.button_id,
aapb.button_template,
aatb.template
from apex_application_page_buttons aapb,
apex_application_temp_button aatb
where aapb.button_template_id = aatb.button_template_id
and aapb.application_id = [Your App ID]
and aapb.button_template is not null
and aapb.button_position = 'Region Position'
and upper(aatb.template) like '%#BUTTON_ID#%'
order by 1,2,3

This will not detect where you may have referenced the ID in other code (such as JavaScript code), it just identifies the buttons that could be problematic. So you can then review the pages returned by this query, to isolate any pages that could have issues.

The easiest way to fix the issue will be to use the 'Button ID' value returned from the query as the new 'Static ID' for the button. This would mean the button would be rendered with the same ID as prior to release 4.1 and any dependent code would still work.

This will of course be documented in our Release Notes as Changed Behaviour, but I hope this post helps to give a little pre-warning as to whether this may affect you.

Updated: Thank you to Louis-Guillaume Carrier-Bédard for pointing out that the query I (rather stupidly!) initially added does not work in APEX 4.0. My apologies if this caused any inconvenience.

APEX 4.0 Enhancements: Validating Form Data

2010-08-05T13:48:00.041+01:00

Oracle Application Express 4.0 introduces lots of big new features; websheets, dynamic actions, plug-ins, RESTful web services, team development, updated charts, the list goes on. But there are also many enhancements to existing functionality that we hope will help to simplify the overall process of developing applications in APEX. One such area, and the focus of this post is how data is validated. This post will give you an overview of what's changed with validations and how these changes will make your daily development life a little easier.

Item-Centric Validation
Historically in APEX if you want to validate data input on a page, you create a validation. The validation is a separate component that you need to define and maintain. Now in APEX 4.0, the actual item can handle some simple validation of the data it receives. For example, all item's (both native to APEX and plug-ins) now have a 'Value Required' attribute. By setting this to 'Yes', APEX will automatically validate a value has been entered and raise an error if not, no separate validation required.

Additional to this 'Value Required' validation, some item types also validate their data, based on how the item is defined. For example, the new 'Number' item type, which you can use for handling numeric data contains settings for 'Minimum Value' and 'Maximum Value'. When these are defined, APEX will automatically validate the data received based on these settings and raise appropriate errors.

Settings for the new 'Number' item, settings highlighting are automatically validated

The new datepicker item in APEX 4.0 also supports this type of automatic validation. Additional to the 'Value Required' setting (available for all items), the datepicker also allows setting 'Format Mask', 'Minimum Date', 'Maximum Date' and 'Year Range'. In doing so, APEX will again automatically validate the data received based on these settings and raise appropriate errors.

Settings for the new 'Date' item, settings highlighting are automatically validated

Plug-in items may also contain automatic validations, depending on whether the plug-in author has coded in this support.

You also have a couple of ways of customising the error message that is displayed by APEX when these validations fail, in terms of content and position. To override the default error messages, please refer to this section of the user guide. This details all of the 'Text Messages' that you would need to define in your applications to override the defaults. If you want to change where the error is displayed on screen, please see the 'Default Error Display Location' attribute available via 'Edit Application Properties' on the application homepage.

Finally, debug mode has also been enhanced to show when these validations are executed and whether they passed or failed.

Item based validations offer a more logical approach to validating data and requires fewer moving parts, which means less to define and less to maintain. Of course, for other more complex situations a separate validation will still be required, but this greatly simplifies some of the more common, simple scenarios.

Button-Centric Validation Exclusion
Again, historically in APEX if you don't want a validation to fire when certain buttons are pressed, you would define that logic in the validation. Let's take an example. If you have a typical 'Form' page used for inserting, updating and deleting data, you may want your validations to fire for insert and update, but not for delete. This would involve going through each validation and setting some condition such as where REQUEST != 'DELETE' or similar, to prevent the validation from firing. Now, in APEX 4.0, the actual button can be defined to either 'Execute Validations' or not.

Specify that pressing the button should not cause any validations to fire.

This is much easier, all you need to do is set this at button level and that's it, no item, plug-in or custom validations will fire. Wizard created forms will set this up for you automatically, so when creating these types of forms, the 'Create' and 'Save' buttons execute validations and the 'Delete' and 'Cancel' buttons do not.

You can also override this at validation level by setting the 'Always Execute' validation attribute to 'Yes' (defaults to 'No'). This could be useful for example if you want to always execute a security check, regardless of any button exclusions.

Debug mode has again been enhanced here to show if validations are prevented from firing because of the button setting.

Tabular Form Validations
APEX 4.0 now also supports declarative validation of tabular form data. Before APEX 4.0, there was no declarative support for validating this type of form and you would have to do a lot of manual PL/SQL to validate your data. Currently, tabular form validations only support a subset of what's available with page item validation, but do cater for some of the more common scenarios (required values, type checks and string comparisons). We are looking to extend this in a future release of APEX.

Error Message Label Placeholders
This is small but one of my favourites. When defining an error message that displays when a validation fails, if the validation is associated with a specific page item, you can now use the #LABEL# placeholder to dynamically reference the associated item's label.

Use #LABEL# instead of hard-coding the associated item label text.

So instead of having to duplicate the label text in the error message (and have to remember to change it if you change the item's label) as was historically the case, just use the #LABEL# placeholder and that's it. Again, less to define and less to maintain. An equivalent placeholder is also available for the new tabular form validations, #COLUMN_HEADER#.

Upgrading Applications
So finally, what about your existing APEX applications that have been long since built, where you want to take advantage of some of these new features. Well, take a look at the 'Upgrade Application' feature available via the 'Utilities' menu from the application homepage. This assists you in upgrading your application to use some of the new features in APEX 4.0.

Of particular relevance to validations are the following upgrade types:

Update Text Field Item to Number Field Item, where appropriate - Locates where you have an unconditional 'Is Numeric' validations on 'Text Field' items and upgrades them to use the 'Number' item type with in-built numeric checking. Also removes the now redundant separate validation.
Update Value Required item attribute to Yes, where appropriate - Locates where you have unconditional 'Not Null' validations on items and sets those item's 'Value Required' attribute to 'Yes'. Also removes the now redundant separate validation.
Numeric, Required and Date Picker Item updates based upon conditional validations - Just locates where you have conditional validations for 'Is Numeric', 'Not Null' or 'Is Valid Date' on 'Text Field' items, for your manual review. So you can determine if the validation can be replaced with some item settings and button exclusions.

So quite a few little enhancements that hopefully add up to easier and more intuitive data validation. Good luck with your new APEX 4.0 style validations and let us know what you think!

Many thanks to Patrick Wolf for reviewing this post and filling in the gaps.

APEX 4.0 - Do you want to know more?

2010-08-03T13:32:00.007+01:00

Do you want to ask the Vice President of Database Tools at Oracle and original developer of Application Express a question about Oracle Application Express 4.0?

Mike Hichwa is going to be interviewed by Oracle Profit Magazine on APEX, so if you have something you want to ask, questions are being collected for consideration via Twitter. Tweet your questions to @OracleProfit, with the hash tag #askprofit. Selected submissions will receive a 1GB flash drive — and be printed in the November issue of Profit Magazine.

Report filtering with APEX 4.0, dynamic action style

2010-07-21T08:45:00.034+01:00

I've just got back from the APEX SIG event in Birmingham. As ever, the event was well organised and attended and it's always good to catch up with the extremely knowledgable and enthusiastic UK APEX community. If you haven't yet attended one of these events then I'd definitely recommend them, you will undoubtedly learn something new, make some great contacts and enjoy the day! The next event is planned for 3rd November at the Oracle City Office in London, so if you're interested in coming along, please see here.

Down to business. During the event I was asked whether dynamic actions in APEX 4.0 can handle Ajax based filtering of report data. The answer is yes, so thought I'd make the most of the train journey home to blog about how. The approach is slightly different depending on whether you're dealing with classic or interactive report regions. In this post, I will detail the steps to achieve this with Interactive Report Regions (IRRs), fully declaratively without coding 1 line of JavaScript.

You can also see this working in my sample application here. This example shows a report on the EMP table, with quick filters available to filter by department or job via Ajax, partial page refresh.

The following steps assume that you have the EMP and DEPT tables in your current schema.

With IRRs, the basic approach involves the following 4 steps:

Create a page with a report region, ensuring the page item filters are referenced in the SQL.
Create the page items for filtering.
Create the dynamic action to fire whenever any of the page item filters change value, to refresh the IRR.
Define the IRR to save these items values in session state after it is refreshed.

Now let's walk through these steps in detail.

Create the page

Within an application, click the 'Create Page' button.
Select 'Report', click 'Next'.
Select 'Interactive Report', click 'Next'.
For 'Page Attributes', enter 'Employee Report' for 'Page Name' and 'Region Name'. Importantly, you must also ensure that the 'Region Template' is set to some template that contains the #REGION_STATIC_ID# substitution string, otherwise this example will not work (dynamic actions need this ID to be able to perform the refresh). Most of the new themes now default to an appropriate template when creating new IRRs (as defined by the new Theme 'Region Default' for 'Interactive Reports'). However if you're using an old theme then you may need to select an appropriate template (try 'Region without Buttons and Title'). Also make a note of the page number (I'll assume page 1 for these steps) and click 'Next'.
For 'Tab Attributes', leave as default and click 'Next'.

For 'Enter a SQL SELECT Statement', enter the following query (referencing the page number noted in step 4 when defining the item names) and click 'Next':

select e.empno,
e.ename,
d.dname,
e.job,
e.hiredate,
e.sal,
e.comm
from emp e,
dept d
where e.deptno = d.deptno
and e.deptno = nvl(:P1_DEPTNO, e.deptno)
and e.job    = nvl(:P1_JOB, e.job)

Click 'Finish'.
For purposes of this example, we're going to switch off the standard 'Search' bar to keep things clean. You can do this by right clicking on the 'Employee Report' in tree view and selecting 'Edit Report Attributes'. Then in the 'Search Bar' region, select 'No' for 'Include Search Bar' and 'Apply Changes'.

Create the page items

Go to edit the page. We'll create these items in a separate 'Quick Filters' region above the IRR. In 'Tree View', right click on the region position where the IRR has been created (by default this is 'Body (3)' and select 'Create'.
Select 'HTML' and click 'Next'.
Select 'HTML' and click 'Next'.
For 'Title', specify 'Quick Filters', for 'Region Template' select 'Report Filter - Single Row' and amend the 'Sequence' to be say 5, so that this appears before the IRR on the page, click 'Next'.
For 'Region Source', leave blank and click 'Create Region'. This has created a blank region, that will be the container for our filter items. We'll create 2 filters, to filter the employee report by the job and department columns respectively.
In 'Tree View', right click on the 'Quick Filters' region and select 'Create Page Item'.
Select 'Select List', click 'Next'.
For 'Item Name', enter 'P1_DEPTNO', click 'Next'.
For 'Item Attributes', accept defaults and click 'Next'.
For 'Settings', accept defaults and click 'Next'.
Ensure 'Display Null Value' is 'Yes'.
For 'Null Display Value' enter '- Show All -'.
Leave 'Null Return Value' leave as blank, this will be treated as a true null by APEX.
For 'List of Values Query', enter the following query and click 'Next':
```
select dname d,
deptno r
from dept
order by 1
```
For 'Source' accept defaults and click 'Create Item'.
Now for the job select list. In 'Tree View', right click on the 'Quick Filters' region and select 'Create Page Item'.
Select 'Select List', click 'Next'.
For 'Item Name', enter 'P1_JOB', click 'Next'.
For 'Item Attributes', accept defaults except for the 'Begin on New Line' attribute. We want to set this to 'No', so that the filters appear on the same line in the 'Quick Filters' region. Click 'Next'.
For 'Settings', accept defaults and click 'Next'.
Ensure 'Display Null Value' is 'Yes'.
For 'Null Display Value' enter '- Show All -'.
Leave 'Null Return Value' leave as blank, this will be treated as a true null by APEX.
For 'List of Values Query', enter the following query and click 'Next':
```
select distinct job d,
job r
from emp
order by 1
```
For 'Source' accept defaults and click 'Create Item'.

Create the dynamic action

Go to edit the page. Right click on the 'Dynamic Actions' tree node and select 'Create'.
Select 'Advanced' for the 'Implementation'. We need 'Advanced' here because 'Standard' only supports the 'Show', 'Hide', 'Disable' and 'Enable' actions.
For 'Name', enter 'QUICK FILTER REFRESH', click 'Next'.
For 'When', leave the event as 'Change' and for 'Item(s)', enter 'P1_DEPTNO,P1_JOB'. Leave 'Condition' as 'No Condition'. These selections define the dynamic action will fire whenever either the department or job select list's value changes. Click 'Next'.
For 'Action', select 'Refresh' under the 'Component' category in the select list. The 'Refresh' action currently supports IRRs, classic reports, all item types with cascading LOV support and may also support item or region plug-ins, depending on whether the plug-in author has coded the plug-in to support this (the plug-in documentation should state if this is supported). Charts in APEX 4.0 are not yet supported.
Ensure the 'Fire on Page Load' checkbox is unchecked, there is no need to refresh the report when the page loads, it's already fresh. Click 'Next'.
On the 'Affected Elements' page, we'll define what will be refreshed. Select 'Region' from the 'Selection Type' select list and 'Employee Report' from the 'Region' select list. Click 'Create'.

Define the IRR to save these items values in session state after it is refreshed

Right click on the 'Employee Report' and select 'Report Attributes'.
Go the 'Advanced' and for 'Page Items to Submit', enter 'P1_DEPTNO,P1_JOB'. Click 'Apply Changes'.

So that's it, run the page and you will now see the filters are fully functional. Select different jobs and departments and see the report refresh, showing employees scoped by your selections. This example could be easily extended to have different item types used as the filters, not just select lists. A common example would be a slider component where you select a value or range of values, upon which the report is refreshed with rows specific to your selection. I will try and add that to my sample application when I can.

For classic reports, this is slightly more complicated than as outlined above, as there is no 'Page Items to Submit' attribute (yet!!), so you need to save the values in session state in a slightly different fashion. I will try and add that too when I can. (Hint: Instead of defining the IRR 'Page Items to Submit' as detailed in the last bullet, use the 'Execute PL/SQL Code' action within the dynamic action, to fire before the 'Refresh' action. Just leave the code as 'null;' and set the 'Page Items to Submit' attribute to your page items. This will work, but unfortunately issues 2 Ajax calls to do so, which is not optimal.)

Also, if you're interested in seeing more examples of refreshing reports with dynamic actions, I have a couple of others in the sample application:

Refresh - Showing an alternative approach for user's to select their 'Saved Reports' in IRRs. Instead of using the default select list in the search region, this has been switched off in favour of a separate report on the left of the page containing the saved reports available to the current user. This report also shows a tooltip for the report description (if defined), when hovering over the report name. The dynamic action is used to refresh this saved report list, so if a user saves a new report or deletes a report, the report list is updated. Again this is all via Ajax and does not require any manual JavaScript coding.
Refresh 2 - Showing how report row deletion can be handled via Ajax with dynamic actions. This example does require a couple of places within dynamic actions where a line of JavaScript is required.

To learn more about these examples, please download the application, install in your workspace and have a closer look.

APEX 4.0 - New Dynamic Action Sample Application

2010-07-08T11:10:00.012+01:00

In Application Express 4.0, we have introduced a new feature called 'Dynamic Actions', which enables you to define client-side behaviour declaratively in APEX. In order to try and help people gain understanding in some of the possibilities this feature offers, I have created a new dynamic action sample application featuring many different examples of both native and plug-in dynamic actions.

You can access this application online here.
Alternatively, you can download this application and install it in your own workspace, which I would recommend so you can really see what's going on. Download here.

Please note: Currently the drag and drop example doesn't work in IE, I will try and fix this up when I have time.

Useful Links:

To sign up for a free workspace and try out APEX 4.0 for yourself, please click here.
To learn more about this and other new features in Oracle Application Express 4.0, please visit our New Features page.
Dynamic action official documentation
To follow the Oracle Dynamic Actions OBE, which offers an excellent starting point, click here.

I welcome any feedback you may have and will try and blog some more about some of the specific examples when I can.

Anthony.

APEX 4.0 - Learn more about Dynamic Actions

2010-01-15T02:33:00.022Z

Update: Please note, I have now updated my dynamic action sample application, the links in this post no longer work. Please see this blog post for details.

As many of you may know, APEX 4.0 Early Adopter's was released before Christmas. In this release, we introduce a new feature called 'Dynamic Actions' that provides a declarative way of defining client-side behaviour, without needing to know JavaScript. There is a simple wizard to create new dynamic actions, whereby you just specify 'When' the dynamic action will fire, the 'Action' itself and 'What' will be affected. As I said, you don't have to know JavaScript to do a fair amount with this feature, but there are also some hooks for JavaScript developers to extend the dynamic action framework to do a whole load more!!

To help you understand this feature, I have put together a sample application containing lots of different uses of dynamic actions such as drag and drop, styling page items and interactive reports, retrieving data from the server via AJAX, responding to plug-in item events such as the 'Slider' sliding and more. You can either view the application running on the EA instance here or download it from here, so you can install it in your own EA workspace and have a deeper look. If you haven't yet signed up for the APEX 4.0 Early Adopters, take a look at David Peake's related blog post where he explains how to get started.

Note: If you are installing this application, there is one supporting object defined containing a simple PL/SQL function 'getCommission' used by a couple of the examples. During the install, please install this supporting object to get the full functionality. Also, the application requires that you have a copy of the standard 'EMP' table in the parsing schema for the application.

The application makes use of a number of native dynamic actions (that will be built-in to APEX), but also contains 5 dynamic action plug-in examples which you can look at, install and play around with. The plug-ins are:

Draggable - Define page elements as draggable, with various options such as restricting by vertical or horizontal axis, transparency during drag, containment and more.
Droppable - Define page elements as droppable, with various options such as restricting which draggables can be dropped, styling to guide the user where they can drop the element and more.
Execute PL/SQL Code - Define a PL/SQL snippet right from within the dynamic action that will be executed on the server, via AJAX. This is currently only coded for Theme 1.
Highlight - Patrick Wolf's plug-in that provides the ability to highlight elements on the page.
Stripe Report - Used for striping interactive report regions with alternate row colours.

I have ensured all the plug-in code is thoroughly commented to try and help you understand exactly what's going on and hopefully get you started in building your own dynamic action plug-ins!!

Plug-ins are 1 of the major components of APEX 4.0 and if you're interested to learn more I can thoroughly recommend taking a look at Patrick Wolf's 'How to create a plug-in' blog post and accompanying downloads.

I hope you like the application and let me know how you get on!!!

Anthony.

PS: Many thanks to Patrick Wolf for his invaluable help in reviewing these plug-ins.

Out Now!! Application Express 3.2.1

2009-08-26T10:28:00.006+01:00

The Oracle Application Express 3.2.1 patch set is now available for download and provides not only fixes to the following bugs, but also some additional functionality and considerations as summarised by Joel and detailed in the patch set notes.

You can get hold of it by either:

Downloading the full version from OTN.

Download the patch set 8548651 from METALINK.

If you're upgrading from any APEX version pre-3.2, then you'll need to use the full OTN release. Otherwise if you're upgrading from 3.2, then you only need the patch set.

Also in this patch set, we have included an additional documentation chapter, entitled Accessibility in Oracle Application Express. This aims to provide information for users who are accessing Oracle Application Express utilizing only a keyboard or Freedom Scientific's screen reader JAWS. It details the current accessibility issues in APEX and shows workarounds where they are possible. (We hope to address a number of these issues in APEX 4.0.)

I would be very interested to hear from anyone who uses APEX with keyboard only, screen reader or other assistive technology to get feedback on how we can hopefully get better at being accessible to our users with disabilities. Also if you use APEX to build applications that have strict accessibility requirements and have feedback on your experiences then I would love to hear from you also.

Please drop me an email at the email address in my profile if you would like to talk about this.

Anthony.

OTNs APEX Developer Competition 2009

2009-07-01T11:22:00.005+01:00

Are you the...

  ...travelling type?  Fancy winning a free ticket for Oracle OpenWorld in San Francisco (October 11 - 15) to meet with like minded APEX enthusiasts and learn more about APEX and other Oracle technology?

Or maybe more the...

  ...bragging type?  How would the words 'Oracle Application Express Developer Competition Winner 2009' look on your CV? It does have a certain ring to it, don't you think?

Or even the...

  ...academic type?   What about the prospect of furthering your understanding of APEX by paging through your winning copy of 'Pro Oracle Application Express'?

Whatever your reasons, enter the OTN 'Oracle Application Express Developer Competition 2009' by submitting an APEX application that stands out from the crowd and you could be in with the opportunity of winning one of these great prizes or accolades!

For more information, including submission guidelines, all important judging criteria and registration details, please visit the OTN page and David Peake's related post. Entries close 24 August, 2009.

Good luck!

APEX SIG in the UK - Finally!

2009-01-22T10:22:00.015Z

So after much discussion and a lot of hard work from Justin Hudd from e-DBA, the 1st APEX SIG event in the UK is now officially going ahead and booked for February 13th at the Oracle City Office in London. The agenda is as follows:

Introduction - Justin Hudd (e-DBA)
Latest & Greatest - David Peake (APEX Product Manager)
APEX @ Oracle - Learn How Oracle Utilises Application Express (Me)
What happens on an APEX Page? - Dimitri Gielis (APEX Evangelists)
Using APEX to Expose your Business to the Web - Matt Nolan & Vincent Migue (e-DBA)
Dispelling Myths about Application Express - John Scott (APEX Evangelists and author of Pro Oracle Application Express)
APEX Forum – A Chance to ask the Experts

Then the usual post event drinks at a nearby drinkery.

If your interested in coming along then you'll need to register for the event via the website. Pricing varies on the following:

If you are already a member of UKOUG then it's free to attend (and you can buy additional places at the members rate of £80 plus VAT for colleagues).
If you are not a member then they have setup a non-member rate of £160 plus VAT.

There are only 55 places available so attendance is limited.

Hopefully see you there!

'Publish to APEX' from SQL Developer 1.5.3

2008-12-12T11:22:00.020Z

Since SQL Developer 1.2.1 and APEX 3.0.1, we've had some useful integration between APEX and SQL Developer, the ability to import and deploy applications, browse the APEX metadata, remote debug PL/SQL in your applications and more. With the latest release of SQL Developer 1.5.3, it is now possible to create a quick and simple APEX application from within SQL Developer (thanks to a bug fix). This is possible through the 'Publish to APEX' feature and creates a simple 1 page APEX application containing an Interactive Report Region based upon a SQL statement.

(Note: Requires APEX 3.1 or above.)

This feature allows you to take any grid of data, right click on it and select to 'Publish to APEX'. (Note a 'grid of data' includes results from executing a SQL statement, results of pre-defined or user-defined reports from the 'Reports' tab, specific table / view properties such as columns, data, constraints etc. and I'm sure there are more.) Upon selecting 'Publish to APEX', the following dialog is displayed:

This dialog allows you to specify 5 properties:
1) Workspaces- The workspace where you want the application to be created (this list will only display workspaces that are associated with the schema of the context of your current grid of data).
2) Application Name - The name of the application that will be created.
3) Theme - The theme for your new application, specifying look and feel.
4) Page Name - The name of the page that will be created.
5) SQL - The SQL that will be used to generate an interactive report region within the page. This defaults to the SQL used to build the original grid of data, but can be changed.

Upon clicking 'Apply' SQL Developer will create the application and show a dialog with some basic creation information such as application name and ID. This creates a 1 page application in the workspace defined, containing an interactive report region with a source of the SQL specified in the dialog.

A few more points to note about this application:
- The application's authentication scheme defaults to 'Database Account Credentials' meaning that you'll need to authenticate into the application using valid database account username and password. This can obviously be changed to something else if required.
- The application will be created in an application group called 'Published from SQL Developer'.
- The interactive report region only displays the first ten columns of the report by default, but again this can easily be changed via the interactive reports menu bar 'Actions' green cog drop down, then select 'Select Columns'.

Here is an example of the application that is generated. I selected to publish data that showed access attempts to my workspace (selecting from the APEX metadata view apex_workspace_access_log). I then used interactive report features to refine my data to show all the failed login attempts for applications within a workspace, grouped by application:

Have fun publishing to APEX!!!

Out Now!! Application Express 3.1.2

2008-08-29T08:21:00.008+01:00

APEX 3.1.2 is now available for download and fixes the following bugs. You can get hold of it by either:

Downloading the full version from OTN.

Download the patchset 7313609 from METALINK.

If your unsure about which download you need, please read Joel's blog. As Joel says, if your upgrading from any APEX version pre-3.1, then you'll need to use the full OTN release (which includes the version that comes pre-installed with 11gR1).

For details on future plans, please take a look at our Statement of Direction, which was updated just under 3 weeks ago with details of the 'Forms Converter' of APEX 3.2 and 'Websheets', 'Updateable Interactive Reports', 'Extensible Item Framework' and much more of APEX 4.0.

Happy patching!
Anthony.

APEX with eBusiness Suite survey reminder

2008-08-07T15:58:00.006+01:00

Readers of the APEX blogosphere will already know, we are currently conducting a survey in an attempt to better understand how people are using Application Express with Oracle eBusiness Suite / Oracle Applications. For example, have you implemented a killer reporting module with our 'Interactive Reports' to empower your users to get better information from their eBusiness Suite application data? Have you had other integration successes?

If so, this is just a quick reminder to anyone who hasn't yet completed David Peake's single page survey, please if you can take a moment to fill this in and tell us your thoughts, we would much appreciate it. Information provided is completely confidential, unless you state otherwise.

Thank you!
Anthony

Dynamic Date Filtering in Interactive Reports

2008-07-16T12:26:00.032+01:00

Update: Please note, in Application Express 4.0, it is now possible to define a 'Row Filter', where you can reference SYSDATE directly from the filter. For further information, please see the 'Adding a Row Filter' section of the user guide.

Introduction...
Whilst the filtering part of interactive reports currently supports a fair number of options, there isn't currently the facility to specify a dynamic date filter using SYSDATE. A common use case for this would be a dashboard report where you want to display all the overdue items in a dataset. In the interactive report filter definition, it would look something like this...

But unfortunately, we do not currently support the use of SYSDATE in the Expression field. So what can you do? I will discuss 3 possible workarounds for achieving this.

Note: Method 1 does not require any changes from a developer perspective, methods 2 and 3 require some developer work.

How...
Method 1
Use the 'Is in the last' operator, and then specify some arbitrarily large amount (100 years).

This could then be saved by the user as a 'Named Report' for future use. Although this is the quickest and easiest method, it isn't very intuitive for the user to come up with and also it's not 100% water tight to use this filter, as it might be possible that a date is entered outside of the arbitrary range specified.

Method 2
It's possible to specify filters on interactive reports via the URL. This uses the 'IR%' prefix in the 'Item Names' argument in the URL syntax, as detailed in the documentation. This method involves dynamically provisioning a link to the interactive report page and could be implemented by creating a SQL report to look like a menu, like this:

1) Add a region of type 'SQL Report' and for the 'Region Source', specify:

select sysdate from dual

2) Ensure the following is set during the wizard creation:

- Report Template: template: [theme number].Borderless

3) Go the 'Report Attributes' page and specify the following:

- Pagination Scheme: No pagination.
- Untick the 'Show' checkbox for the SYSDATE column.
- Click 'None' for 'Headings Type'.

3) Add a column link to the report by going the 'Report Attributes' page and clicking the 'Add Column Link' link from the 'Tasks' menu on the right. For this column link, specify the following values:

- Link Text: 'Overdue Reviews'
- Page: 2
- Clear Cache: '2,CIR'
- Item 1 Name: IRLT_REVIEW_DATE
- Item 1 Value: #SYSDATE#

Note: My interactive report is on page 2 of my application, but change all occurences of 2 to to whatever your page is.

This generates a link like this:

Of particular interest here are...

Clear Cache = '2,CIR'
This clears the cache for page 2, and also uses the new 'CIR' parameter, which clears all settings that could already have been applied to the interactive report (including any default report settings).

Item Names = 'IRLT_REVIEW_DATE'
This can be broken down as follows:
- 'IR': This is used to set interactive report filters dynamically over the URL.
- 'LT': The characters directly after the 'IR' prefix are equivalent to the 'Operator' value in the interactive report 'Filter' dialog, so in this case 'LT' specifies a 'Less than' operator. Note: You could also use 'LTE' which would specify a 'Less than or equal to' operator. See here for more options.
- 'REVIEW_DATE': Then after the underscore, this is the column name on which to apply the filter, equivalent to the 'Column' value in the interactive report 'Filter' dialog. In this case we are filtering on the 'REVIEW_DATE' column.

This method works well, but does require some sort of separate menu or link to go to the interactive report, which steps out of the normal use-case for interactive reports.

Method 3
The final method requires the developer to code a derived column to calculate if the record is overdue in the report SQL. A snippet something like:

(case 
   when review_date < sysdate then 'Yes' 
 else 
   'No' 
 end) review_overdue

Once this has been done by the developer, the user could then choose to apply a filter on this column as shown in the screenshot:

This could then be saved by the user as a 'Named Report' for future use.

Note: With methods 1 and 3, the developer could also default these reports for users, saving the report as 'Default Report Settings' in the 'Save Report' dialog. This means that when any user logs into and sees this report, they will default to these filters. One consideration here though, when the developer saves the 'Default Report Settings', you cannot specify a 'Name' for the report, so the user would see the filter as it is coded without a named tab.

Conclusion...
So method 3 would be my preferred choice, because it works and I believe provides the most user friendly solution for the users whilst keeping within the standard use-case for interactive reports. One consideration of this approach though would be that no index would be used on that column (see comments).

A couple of questions for the community...

Would you find it useful to be able to filter by SYSDATE?

Would you find it useful to be able to filter not just by a value, but by another column value?

What else would you like to see incorporated into Interactive Reports to make them even better?

Further Reading...
General overview and information on interactive reports
Marcie Young's Advanced Interactive Report Tutorial
APEX documentation page on dynamic interactive report filtering via the URL
David Peake's (APEX PM) blog post on 'Advanced Interactive Reports

New job...

2008-07-10T13:59:00.003+01:00

Just a quick note to say I've recently made a commitment to Application Express and joined the APEX development team here at Oracle. I am very excited to work for a cracking team on a great product and look forward to blogging much more about it in the future.

Anthony.

Dynamic Quick Picks - APEX style!

2008-02-10T19:06:00.002Z

Update...
Following a discussion on the APEX forum regarding this post, I have improved the QUICK_PICK procedure so that it can handle a query containing bind variables. This is making use of the in-built function WWV_FLOW_UTILITIES.GET_BINDS(), with a technique proposed in the forums here.

Introduction...
Have you ever admired the quick pick links that appear on some of the APEX pages under select lists. For example editing the 'Display As' property of a page item you can quick pick common display types, as shown here:

So, I wanted to implement this same feature in my own applications, only dynamically generating the quick pick options based on data. For example, in the standard EMP table there is a column called Job. I wanted to display 2 quick picks for this field, based on the top 2 occurences of values for jobs in the EMP table.

How...
Step 1) Firstly we need a generic database function to handle rendering the links. Here is the code:

create or replace
PROCEDURE QUICK_PICK
( p_query VARCHAR2
, p_item VARCHAR2) AS
  TYPE cur_type IS REF CURSOR;
  cur cur_type;
  v_display VARCHAR2(4000);
  v_return VARCHAR2(4000);
  l_names DBMS_SQL.VARCHAR2_TABLE; 
  l_query VARCHAR2(1000);
BEGIN
  l_query := p_query;
  --get any binds and replace with the value from session
  l_names := WWV_FLOW_UTILITIES.GET_BINDS(l_query);
  FOR i IN 1..l_names.COUNT LOOP
  l_query := REPLACE( LOWER(l_query),
                      LOWER(l_names(i)),
                      '(select v('''|| 
                        LOWER( LTRIM(l_names(i), ':')) ||
                        ''') from dual)');
  END LOOP;
  HTP.P('<div>');
  OPEN cur FOR l_query;
  LOOP
    FETCH cur INTO v_display, v_return;
    EXIT WHEN cur%NOTFOUND;
    HTP.ANCHOR('javascript:setValue(''' || 
                p_item || ''',''' || 
                v_return || ''');',
    '[' || v_display || ']',
    null,
    'class=''itemlink''');
  END LOOP;
  CLOSE cur;
  HTP.P('</div>');
  HTP.P('<br/>');
END QUICK_PICK;

So this procedure takes a SQL query (p_query) and a page item ID (p_item) as parameters. The results from the query specified in p_query will be used as the display and setting values of each quick pick link, so the query must contain two columns in the select statement (the first selected column should be the display value to be used and the second the return value, see query below for an example). The item name specified in p_item will be the item that the quick pick link will set.

This procedure just opens a dynamic cursor based on the query specified in the p_query parameter. Then calls the HTP.ANCHOR function to generate the anchor HTML tag. The procedure also pads the lot in a div tag and adds a line break at the end for presentation (you may want to change this if you have another page item on the same line as the select list being set). The following is an example of the HTML generated by this procedure:

<div>
  <a class="itemLink"
     href="javascript:setValue('P2_JOB','SALESMAN');">
        [SALESMAN]
  </a>
  <a class="itemLink" 
     href="javascript:setValue('P2_JOB','CLERK');">
        [CLERK]
  </a>
</div>

Step 2) Secondly, we need to call this function in APEX. You can do this by adding a page item with the following settings:
Name > Name: [whatever you want, P1_QUICK_PICK_JOB would be fine for this]
Name > Display As: Display As Text (based on PL/SQL, does not save state)
(Note: This display type is not available when creating a page item, so it must be set after creation.)
Displayed > Sequence: [just after the select list item you wish the quick pick link to set]
Label > Label: [blank]
Label > Template: No Label
Source > Source Type: PL/SQL Anonymous Block

BEGIN
  QUICK_PICK('SELECT  summed_rows.job display
              ,       summed_rows.job return
                      FROM  (SELECT    job,
                                       SUM(1) qty
                             FROM      emp
                             WHERE     job IS NOT NULL
                             GROUP BY  job
                             ORDER BY  SUM(1) DESC
                             ) summed_rows
                      WHERE  ROWNUM    <= 2'
                    , 'P2_JOB');
END;

This example uses a query that selects the top 2 occurences of job values in the emp table. The second parameter indicates that the page item 'P2_JOB' will be set by the quick pick link.

Step 3) So finally, there is one more thing to do, create the css used by the link. I have copied the inbuilt class used by APEX to render these links and re-specified it in its own 'custom.css' file. You can do this by creating a file with the following contents:

a.itemLink:link {
color:#811919;
font-size:11px;
margin:0pt 5px 5px 0pt;
text-decoration:none;
white-space:nowrap;
}

Then save this as 'custom.css' and upload it to APEX via 'Shared Components > Cascading Style Sheets'. Finally to make it available to the application, we need to reference this in the page template, so add the following line to the 'Definition > Header' code, after the default theme css declaration:

<link rel="stylesheet" href="#WORKSPACE_IMAGES#custom.css" 
      type="text/css" />

The results can be seen here:

Conclusion...
So that's it. Then in order to add more quick pick links to other select items, just create other page items referencing the QUICK_PICK database procedure as detailed in step 2.

Limitation: Currently the css doesn't work in IE, only Firefox, so the links just display as normal text. When I find out why and will post an update.

Hope it helps,
Anthony

UKOUG - My presentation slides are now available...

2008-01-27T18:22:00.001Z

Just a quick post to mention my slides from my UKOUG presentation, 'Building The Rich User Interface with Oracle Application Express and Ajax' are now available to download.

Sorry about the delay for anyone who wanted to take a look at this sooner, I wanted to tidy up some of the code before making it available and have only just had a chance to look at it.

If you are interested in catching this again, I will be doing a very similar presentation at the UKOUG Combined SIG Day at Baylis House in Slough on 27th February.

So come along and say hi!

UKOUG - Day 3

2007-12-10T17:02:00.001Z

So with my presentation firmly behind me, and after having the best night's sleep in weeks, I headed into day 3 looking forward to catching John Scott talk about 'Debugging APEX Applications', Dimitri Gielis present 'Integration of BI (XML) Publisher and APEX (Oracle Application Express)' and also sit on the panel for my first APEX roundtable discussion group.

John's presetation was very good, he presented well and showed numerous techniques for debugging your APEX applications. Specifically of interest was the use of the DBMS_APPLICATION_INFO package which can be used to monitor progress of long running queries / reports and display this progress information back to the user. He also mentioned that as an APEX developer he believes that you have the responsibility to have a good understanding of the database, so as to be able to benefit from the features and build better applications, a point which I totally agree with. If you ever get a chance to watch John present, then do it as I'm positive there will be something in there that you can benefit from. Nice job John!

So then it was time to get ready for the APEX roundtable. I was pretty nervous and left John's presentation quarter of an hour before the end so as to go up to the room and get myself ready. The session was chaired by Jeremy Duggan (Chair of the Modelling, Analysis and Design SIG), and I was on the panel with Dimitri Gielis, Peter Lorenzen and unofficially, but answering a lot of the questions John Scott. Around 20 people turned up, which was reasonable considering it was lunch time! Some of the topics that came up were:

APEX / Forms / ADF

'The Right Tool For the Right Job'

discussing

Validations

ApexLib

article

Statement of Direction

URL Tampering

article

documentation

Page Comments

APEX Builder Plugin

Source Control

JavaScript

unofficially documented

Carl Backstrom

Application Express and Accessibility

Team Development

'Best Practices for Oracle Application Express Collaborative Development'

Dimitri and John after the session.

So that was about it, a very interesting and interactive discussion with loads of input from all the panel and much of the group, Jeremy doing a good job of keeping it all together. And it was great to meet Dimitri and Peter for the first time and catch up with John having met him on day 2.

Unfortunately I had to head back to Reading earlier than expected in the afternoon, so was unable to catch Dimitri's presentation. A very enjoyable day none the less and looking forward to next time!

UKOUG - Day 2

2007-12-10T11:34:00.000Z

It had finally arrived, Tuesday 4th December. A day that had been engrained on my brain ever since receiving 'UKOUG 2007 - Speaker Confirmation' in my Inbox back in July. The day of my first big presentation, 'Building The Rich User Interface with Oracle Application Express and Ajax'.

So very nervous I headed down to Birmingham from Reading and arrived at the ICC at around 9.30am. I was speaking at 11.15 so gave myself a bit of time to register and go over the slides one last time. A worthwhile activity as I was soon to realise on discovering 3 of my slides were missing which I must have deleted the previous night. Thank goodness for backups!!

I headed up to Hall 8b, the venue for my presentation. It was a good size, around 170 capacity but didn't seem to big so I was ok with that. There was no session in there before me so had plenty of time to get setup and settled, or so I thought. The technician arrived soon after me and asked if I was going to be using the supplied laptop or my own, I said my own and he worringly replied, 'You are the first person who's wanted to use their own'. I replied 'Is that a problem?', and he assured it wasn't but there would just be a bit of setup to get it working.

Time went by, people started arriving and my opening slide was still no where to be seen on the big wall behind me. The room of around 50 people was filled with silence, Harrison my session chair did a good job of trying to break it with a quick poll of how many people had used APEX before, which was most (but not all) of the room. I then ran through my agenda which seemed like it took about 5 minutes but probably only lasted about 30 seconds. Anyway, with numerous trips back and forth to the control room by the technician, my opening slide finally appeared on the big wall, a sigh of relief from me and after all the waiting and anticipation, I could talk.

It went ok. I know there are a few things I could have explained better and in more detail. I had always been worried about the amount of technical information I was trying to get across in 1 hour (although originally I was worried I wouldn't be able to fill it!) and in hindsight, I think I should have gone for longer.

So having finished talking in about the right time, there were a few questions and the job was done. John Scott came up to me afterwards and introduced himself. It was great to finally meet him having only known of him before. He said my presentation was really good, which means a lot coming from someone like John and also suggested that I should have a longer time slot to get through everything, I definitely agree.

Me, full of relief!

For anyone who is interested in getting the slides I have currently taken them offline just because I wanted to change a few small things. Once these changes are made I will post a link on my blog. I am also trying to get a demo up and running which will have all the code available to download. Watch this space!

Having done my presentation, and with the weight of an elephant lifted from my shoulders I was then free to enjoy some of the conference.

Really enjoyed Zory Zeharieva of CERN present, 'A Real-Life Experience of Rapidly Building Web-Interfaces with Oracle Application Express at CERN'. She presented well and covered some best practices for building scalable applications which was very interesting and also expressed some concerns around the way APEX application files are managed within versioning systems, a common concern which came up in a few places over the conference.

Also then dropped in on Oracle's Barry McGillin talking about, 'Consolidate Your Microsoft Access Applications to Oracle Application Express'. Barry is a great speaker, informative and easy to listen to (even at the end of a very long day!). He showed how to migrate an Access database with data into an Oracle database and then use the APEX 3.0 feature 'Application Migration Workshop' to build your APEX application. Well worth a look for anyone planning on doing this kind of thing.

So the day of presentations was over, and it was up to the free bar to relax and catch up with some people. Had a very pleasant drink with some colleagues and headed back to the hotel for a good night's sleep.

UKOUG day 2 done and dusted. The months of worry and preparation were worth it and my life can now resume normality. Presenting is, although nerve-wrenching and time-consuming, a very rewarding experience and I would recommend it to anyone.

Problem with 'Not Null' validations

2007-12-02T10:31:00.001Z

Introduction...
A question popped up on the forum this week about the ability to bypass not null validations. The problem is if you define a 'Not null' validation on a page item, the user can enter a blank space and the validation will allow it. But there is a simple workaround and it requires no changes to any of your existing validations.

Note this will only work in APEX version 3.0 or higher. If you are looking to implement this on 2.2 or 2.2.1, then please refer to the link above to the forum post where this was discussed, where I posted a solution for 2.2 (the views were changed slightly). Unfortunately, pre 2.2 this method is not possible, as I'm referencing the APEX dictionary views that were introduced in 2.2.

How...
To get around this you can use an APEX application process to trim all the items for the current page that have associated not null validations. Create the following application process:

Sequence: 1
Process Point: On Submit: After Page Submission - Before Computations and Validations
Name: TRIM_NOT_NULL_ITEMS
Type: PL/SQL Anonymous Block

Process Text:

BEGIN
  FOR cur IN
    ( SELECT  items.item_name
      FROM    apex_application_page_items items,
              apex_application_page_val vals
      WHERE   items.application_id  = TO_NUMBER(:APP_ID)
      AND     items.page_id         = TO_NUMBER(:APP_PAGE_ID)
      AND     items.item_name       = vals.associated_item
      AND     vals.validation_type  
                = 'Item specified is NOT NULL'
    )
  LOOP
    apex_util.set_session_state( cur.item_name
                               , TRIM(v(cur.item_name)));
  END LOOP;
END;

I added this to a form on EMP and setup a not null validation on the job item. Then loaded the page, keyed a space for the job field and on inspection of the debug, you can see that before the process fires, session state shows a " " for P12_JOB, which would have passed the not null validation.

And then after the process has fired, it has been trimmed and set in session to "", which causes the not null validation to correctly fail.

Hope it helps,
Anthony

Debugging with the APEX repository

2007-11-19T12:56:00.000Z

Introduction...
I was recently working on an APEX application on a familiar 'Form on a Table' wizard-built page and encountering a ORA-00957 Duplicate Column Name error, meaning that a column name must be specified more than once in the INSERT.

So the problem must be that I had more than one page item bound to the same database column. This page had got quite bloated with lots of other business logic so I started by turning off regions, the old 'process of elimination', debugging through seeing if there was anything obvious, but nothing sprang up. Then I thought if only there was a way to view all the page items on my page which were bound to the same database column. Well there is, using the APEX repository.

For those of you who are not familiar with the repository, it is basically a set of views that expose all the APEX application metadata. For further information see Have a clean up, utilising the APEX repository which contains links to loads more information.

How...
In the repository there is a view called APEX_APPLICATION_PAGE_DB_ITEMS which is what we are interested in. It is described in the APEX_DICTIONARY view as...

'Identifies Page Items which are associated with Database Table Columns. This view represents a subset of the items in the APEX_APPLICATION_PAGE_ITEMS view.'

We can then run the following query to return all the items for a specific application / page bound to a db column more than once.

SELECT   db_column_name,
         SUM(1) Duplicates
FROM     apex_application_page_db_items
WHERE    page_id = :page_id
AND      application_id = :app_id
GROUP BY db_column_name
HAVING   SUM(1) > 1
ORDER BY SUM(1) DESC

(Note: This view does not contain conditional rendering information, so if this was required you would need to join to APEX_APPLICATION_PAGE_ITEMS on ITEM_ID).

Conclusion...
So if ever you think, I wish I could see this information about this page / report or whatever, you probably can, just take a look into the repository. I wonder if there would be any scope for an APEX debugging framework that defines sets of processes linked to common 'ORA' errors. So for this example, it would simply be:

Error: ORA-00957 - Duplicate column name

Step 1: Run the following query, binding in your page and application id.

SELECT   db_column_name,
         SUM(1) Duplicates
FROM     apex_application_page_db_items
WHERE    page_id = :page_id
AND      application_id = :app_id
GROUP BY db_column_name
HAVING   SUM(1) > 1
ORDER BY SUM(1) DESC

Step 2: Investigate all rows returned from the query and unbind items which should not be bound to the database column.

Step 3: Retest your page.

That would be nice.
Anthony.

Survey results

2007-11-19T12:53:00.000Z

Following the recent survey I conducted on this blog, 'What would you like to read more about on my blog?', the results were:

Developer Tips - 54%
AJAX General - 45%
AJAX with JSON - 45%
BI Publisher Integration - 33%
Access Migration - 0%

I will thus be focusing my efforts on Developer Tips and AJAX related posts in the near future. I was quite surprised at the 0% interest in 'Access Migration' and would have thought this was be quite popular as this is one of the main platforms systems are built on that APEX applications replace. Or maybe it's just that developers aren't using the migration functionality built in to APEX / SQL Developer and just doing it without looking at these. Interesting.

Thank you all for your feedback. More to come shortly.
Anthony.

What do you want to learn about?

2007-09-24T13:14:00.000+01:00

In the interest of trying to write good posts that the APEX community will find useful, I have added a new 'poll' feature to my blog. My first poll is entitled simply, 'What would you like to read more about on my blog?' and I have suggested a few titles to pick from. You can cast your vote by selecting from the check boxes on the right of the page, below 'About Me'.

If you have any other suggestions or areas you wish to learn about or understand better, then please add a comment to this post.

Look forward to hearing from you!
Anthony.

UKOUG 2007 agenda packed with APEX

2007-09-05T16:43:00.000+01:00

The agenda for UKOUG 2007 has just been released and there is currently a total of 13 presentations on or relating to APEX (and a keynote delivered by Tom Kyte!). They are...

Oracle Application Express 3.0: Introduction and New Features with Barry McGillin

APEX and photographs, a mashup made in.... with Dr Jagdev Panesar

Choose Your Weapon – an Overview of Oracle Development Tools with Mr Sten Vesterli

Harvesting the Advantages of a Database Centric Development Approach with Toon Koppelaars

Building The Rich User Interface with Oracle Application Express and AJAX with Anthony Rayner

A Real-Life Experience of Rapidly Building Web-Interfaces with Oracle Application Express at CERN with Zory Zaharieva

Consolidate your Microsoft Access Applications to Oracle Application Express with Barry McGillin

Programming real applications with Application Express with Andrew Woodward

Debugging APEX Applications with John Scott

Integration of BI (XML) Publisher and APEX (Oracle Application Express) with Dimitri Gielis

Apex the extra tinkering to render structures with Derek Scuffell

Oracle Linuxfest: 'Migrating to Oracle 11g on Linux' with Oracle Technology Network

How to Make Your Oracle APEX Application Secure. with Peter Lorenzen

Loads to learn so come along, I'll be there and presentating 'Building The Rich User Interface with Oracle Application Express and AJAX' so looking forward to doing that (scary!) and listening to many more! Hope you can make it!
Anthony.

Developer productivity gains with 'Bookmarklets'

2007-09-03T21:43:00.000+01:00

Introduction...
The ability to manipulate web content with Javascript on the fly is becoming more and more widespread, with tools such as Firefox's Greasemonkey, IE's Trixie and a whole host of other browser add-ons providing the ability to apply scripts to web pages. Bookmarklets are probably one of the simplest and easiest ways to achieve this, storing Javascript code in the URL of a bookmark or favorite. I have built a few which I find helpful in automating some of the more mundane tasks whilst developing with APEX.

How...
All you need to do is drag the following links onto your bookmarks / favorites toolbar in your browser. They are all compatible with Firefox and Internet Explorer.

Bulk Edit - Repad sequence numbers - Sequence numbers by default are padded by 10, but as the development process ensues, items get moved, processes get added and sequence numbers inevitably get scrunched up. This bookmarklet repads sequence numbers by 10 when in bulk edit pages where the 1st column is the sequence number, such as bulk editing page items, computations or validations.

General - Enlarge textareas - Enlarges small textareas for easier editing, useful for 'Region Definition > Source' or 'Page Process > Source'.

Report Attributes - Alignment LEFT - Sets both 'Column Alignment' and 'Heading Alignment' to 'LEFT' whilst on the 'Report Attributes' page.

Report Attributes - Sort All - Checks all the 'Sort' checkboxes whilst on the 'Report Attributes' page.

Once this is done, you can just click the bookmark / favorite when on the appropriate page and the Javascript will be applied.

Conclusion...
In my opinion bookmarklets are fantastic for implementing these type of simple extensions, the main reasons are:

Portability(1) - All my bookmarklets will work in Firefox and Internet Explorer.

Portability(2) - And this is important, using a Firefox add-on such as Foxmarks which synchronizes bookmarks between different instances of Firefox (home and work), your bookmarklets will be available to you on any browser you have synchronized.

Simplicity - Minimal learning curve for deployment, assuming a basic understanding of Javascript and bookmarks.

Categorisation - Easy to organise your bookmarklets into folders and subfolders, just as you would with your regular bookmarks.

Ease of use - I have my bookmarklets available on my bookmarks toolbar, ready for quick deployment.

As APEX developers, we live in a world of 'browser-based development', rather than a traditionally rich IDE such as JDeveloper and Eclipse. The standard browser was not designed to be used as a development tool and thus anything we can do to help increase development productivity, such as write custom bookmarklets or use the essential Firebug can really help. So have a go and write your own, here are some general guidelines including a very useful bookmarklet builder. During development, I find a 'could I automate this?' attitude helps in identifying bookmarklet opportunities.

Let me know if you have any more useful productivity increasing bookmarklets to share and I'll add them to this post.

Anthony.

Update - Another way...
Following the advise of Patrick and Dimitri, I've taken a look at the APEX Builder Plugin and it really is awesome, congratulations Patrick!

It is compatible with both Firefox (with Greasemonkey) and IE (with IE7Pro), and it's fantastic. Whilst bookmarklets are great for a quick fix, if you want to get serious about building your own productivity customisations then I strongly advise you to invest a little time looking at this plugin.

It does not change the APEX base install in any way and just requires the developer to have one of the above add-ons installed in their browser and the plugin scripts available to the workspace, either on the server or stored in the database as static files. Within a couple of hours I had it installed, had a good look around and was building my own extensions.

One of the features of the plugin is the creation of a custom 'Set' select list at the top of any page in the Application Builder. This comes with some useful pre-rolled 'Setters', and also and importantly allows the developer to easily define their own 'Set' options for all or specific pages. This not only makes work more efficient for the developer, it can also be leveraged for enforcing standards across a team developing in APEX.

I was able to recreate the functionality from my bookmarklet, 'Bulk Edit - Repad sequence numbers' on all the bulk-edit pages accessible via the 'Page Definition' page, with the following code:

// Page Items

ApexBuilderPlugin.addSetOperation
  ( "General"
  );
ApexBuilderPlugin.addSetOperation
  (  "Repad sequence numbers"
  ,  { "f01":  function(pField, pRow)
               {
                 return pRow + '0';
               }
     }
  );

For further information on how to do this, please refer to the How to Install guide.

As well as providing the ability to define your own 'set' operations, it also provides the ability to map keyboard shortcuts to common functions, such as F10 for 'Apply Changes' or F8 for 'Run Page' and a much neater solution to the enlarge textarea problem.

Great work Patrick!!

Another Update...
I am now delighted to announce that my 'Repad Sequence Number' customisation will be included in the next official distribution of the plugin. Thanks to Patrick for giving me a mention.

Further Information...
Bookmarklet homepage
General Guidelines
Bookmarklet Builder
Web Development Bookmarklets
Oracle bookmarklets by Eddie Awad for searching documentation
APEX Builder Plugin

New Oracle Magazine APEX / AJAX article

2007-08-17T08:55:00.000+01:00

Just found an interesting article by David Peake (APEX product manager), in the September / October '07 edition of Oracle magazine, entitled 'Express 2.0'. This gives a brief introduction into implementing AJAX based functionality into APEX applications, and then goes onto a step-by-step example on how to implement dynamic tooltips.

Worth a look.
Anthony.

Update: David has just started his own blog so be sure to add it to your feeds!!

Javascript debugging, an alternative to alert()

2007-08-07T18:32:00.000+01:00

Introduction...
This is a simple trick to provide a more flexible approach for displaying debugging output when working on javascript code in your APEX applications. It uses a couple of debugging javascript functions, print() and println() which will show your debug messages in the current page's footer. This allows for multiple messages to be output without having to click through each one, as is necessary with the alert() function. With other benefits too such as being able to copy the text output which is not possible with alert().

How...
1. First add the following line to your 'Page Template > Definition > Footer', at the beginning:

<div id="debug"></div>

(It doesn't really matter where this goes in the 'Footer' code, as long as it's before the closing </body> tag.)

2. Then we need to make the following two javascript functions available for use in your application. (The recommended approach would be to store them in a '.js' file and place this file on your web server.)

//print output messages to your page
function print(p_value){
  $x("debug").innerHTML = $x("debug").innerHTML + 
                          p_value;
}
//print output messages to your page with a line break
function println(p_value){
  $x("debug").innerHTML = $x("debug").innerHTML + 
                          p_value + '<br/>';
}

3. You can then call either print() or println() from within your javascript code.

Happy debugging!
Anthony.

Update...
Do you Firebug? Then there is a simpler way!

If you use Firefox with Firebug, then another way of debugging is to use the built-in Console API. This details some very useful calls such as:

console.log - Writes a message to the Firebug console

console.trace - Writes an interactive stack trace to the Firebug console with information about the calling function and also the parameters passed to the function. It is interactive in the sense that you can click the function name to take you straight to the function source, very nice!

This API provides more calls as well which look very useful, so it's worth taking a look.

(Thank you Carl!)

Checking for Duplicates

2007-06-23T12:04:00.000+01:00

Here is a quick way to check for duplicate records in the database, by using the 'group by' and 'having' sql commands.

The following example uses the trusty 'EMP' table and checks for duplicates in the 'JOB' column.

select   job,
         sum(1) Duplicates 
from     emp
group by job
having   sum(1) > 1
order by sum(1) desc

Or generically this query could be expressed:

select   [duplicate_column_name],
         sum(1) Duplicates
from     [table_name]
group by [duplicate_column_name]
having   sum(1) > 1
order by sum(1) desc

Hope it helps!
Anthony.

AJAX retrieval using Javascript Object Notation (JSON)

2007-06-16T11:36:00.000+01:00

Introduction...
Typically, most of the AJAX examples out there for APEX either use plain text or xml for data transmission. This post details how to implement AJAX functionality using Javascript Object Notation or JSON, retrieving multiple items from an application process. JSON, or the fat free alternative to XML is a simple, lightweight syntax for representing data and can be easily integrated into an APEX application. Due to its light weight, it has performance advantages over using XML and is also very easy to understand and manipulate on the client side.

This post will show how to create a simple example of AJAX with JSON, where a user selects either 'Car' or 'Bike' from a select list, then either car or bike 'models' will be populated into 2 page items.

Pre-requisites...
In order to parse the JSON text and create the javascript objects in your javascript code, you will need to download the JSON open source parser. Once downloaded, it must be made available in your APEX environment. The best way to do this is to save the file into the following directory on your web server:

  ORACLE_HOME\Apache\Apache\images\javascript

And then reference it by adding the following to your 'Page Template > Definition > Header', just after the '#HEAD#' substitution string:

  <script src="#IMAGE_PREFIX#javascript/json.js"
  type="text/javascript"></script>

Alternatively, if you have restricted access to the web server (as with apex.oracle.com), then upload the 'json.js' file as a static file, via 'Shared Components > Static Files', ensure you associate it with your application and then add this reference to the page template:

  <script src="#APP_IMAGES#json.js"
  type="text/javascript"></script>

Note: The static file method will not be as fast in execution as copying the file directly on the server.

How...
Step 1: Create a new, blank page, call it 'AJAX - JSON Demo Page'. (I'll assume it's page 1 for the rest of this example.)

Step 2: Create a HTML, empty region, call it 'AJAX - JSON Region'.

Step 3: Create the following 3 page items:
- The activator will be a select list, where you either select 'Car' or 'Bike'. Selecting one of these options will invoke the 'onChange' event specified and call the 'Get_Multiple_Items()' javascript function, specified in step 5. Set the following:
Type = Select List
Item Name = P1_ACTIVATOR
Region = AJAX - JSON Region
List of Values Query = STATIC2:Car;Car,Bike;Bike
Label = Activator
Element > HTML Form Element Attributes = onChange="get_Multiple_Items();"

(All other attributes just leave as default.)

- Item 1 to return the first model of either 'Car' or 'Bike', set the following:
Type = Text Field
Item Name = P1_RETURN_ITEM_1
Region = AJAX - JSON Region
Label = Return Item 1

(All other attributes just leave as default.)

- Item 2 to return the second model of either 'Car' or 'Bike', set the following:
Type = Text Field
Item Name = P1_RETURN_ITEM_2
Region = AJAX - JSON Region
Label = Return Item 2

(All other attributes just leave as default.)

Step 4: Create an application process via 'Shared Components', set the following:
Name = RETURN_MULTIPLE_ITEMS
Process Point = On Demand: Run this application process when requested by a page process
Type = PL/SQL Anonymous Block
Process Text =

declare
return_item_1 varchar2(100);
return_item_2 varchar2(100);
return_str varchar2(2000) := '';
begin
owa_util.mime_header('text/html', FALSE );
htp.p('Cache-Control: no-cache');
htp.p('Pragma: no-cache');
owa_util.http_header_close;

if :P1_ACTIVATOR = 'Car' then
  return_item_1 := 'Ferrari';
  return_item_2 := 'Ford';
elsif :P1_ACTIVATOR = 'Bike' then
  return_item_1 := 'Suzuki';
  return_item_2 := 'Ducati';
end if;

return_str := '{
                 "model":[
                   "'||return_item_1||'",
                   "'||return_item_2||'"
                 ]
               }';

htp.prn(return_str);

end;

(All other attributes just leave as default.)

Of particular interest here is the following lines of code:

  return_str := '{
               "model":[
                 "'||return_item_1||'",
                 "'||return_item_2||'"
               ]
             }';

So this is where we need to encode the result into the correct JSON syntax, so as to be parsed successfully in the client.

JSON syntax is exactly the same as the syntax used to define normal object and array literals in javascript. For a detailed description of the syntax, see the JSON homepage. In my example, if the user selects 'Bike', the following string would be returned by the process:

  {"model":["Suzuki","Ducati"]}

So here we have a single member called 'model', containing an array of the two return values, 'Suzuki' and 'Ducati'.

Step 5: Create a javascript function on the page
We now need to create the function that will setup, send and receive the request from the server. This will use the built-in 'htmldb_Get' ajax function to call the application process called 'RETURN_MULTIPLE_ITEMS', created in step 4. Set the following:
Page Attributes > HTML Header > HTML Header =

<script language="JavaScript" type="text/javascript">
<!--
function get_Multiple_Items() {
var get = new htmldb_Get(null,
                 &APP_ID.,
                 'APPLICATION_PROCESS=RETURN_MULTIPLE_ITEMS',
                 0);
get.add('P1_ACTIVATOR',$x('P1_ACTIVATOR').value);

gReturn = get.get();

if (gReturn) {
  var myJSONObject = gReturn.parseJSON();
  $x('P1_RETURN_ITEM_1').value = myJSONObject.model[0];
  $x('P1_RETURN_ITEM_2').value = myJSONObject.model[1];
}
}
//-->
</script>

Of particular interest here are the following lines of code:

  var myJSONObject = gReturn.parseJSON();

This calls the function 'parseJSON()' which parses the JSON text to produce an object, and then...

  $x('P1_RETURN_ITEM_1').value = myJSONObject.model[0];

The resulting object 'myJSONObject' can then be accessed like any normal javascript object. In this case, we are retrieving the first value from a member called 'model' (which was created in step 4). This value then sets the 'P1_RETURN_ITEM_1' value.

Now run your page and you should be able to see some nice AJAX and JSON in action! Or take a look here.

Conclusion...
So it's a pretty simple little example, but I really like how easy it is to manipulate the object client-side. The only downfall is that the syntax to encode the JSON string in the application process takes a bit of getting used to, but I found it fairly easy to get my head around. Ideally, you would have a server-side encoder for this and there are many available, just none for PL/SQL that I've found*. There are lots available for Java, which could potentially be loaded in the database and called via a PL/SQL wrapper.

I have not done this for this example because I felt it was overkill for what I was trying to achieve. To just return a few items like I have done here, manually encoding the JSON text was fairly painless, although I'm sure it could quickly get very painful if creating large, nested structures, where a proper encoder may be more beneficial (and performance gains would be more apparent).

Also, bear in mind my example doesn't really do anything in the application process which warrants a trip to the database (just checking hard-coded strings, which could be done client-side). The purpose of this post was to highlight how to implement JSON with AJAX in APEX so I tried to reduce complexity elsewhere to focus on the main points.

Hope it helps,
Anthony.

* Update...
Since writing this article, it appears a PL/SQL JSON library has cropped up from France. I have yet to try it but will take a look and blog about it soon.

Further Information and Links...
Article: 'JSON: The Fat-Free Alternative to XML'
Javascript source file containing JSON parser
JSON homepage, including links to many server-side encoders
JSON wiki
JSON RFC 4627
Article: 'Speeding Up AJAX with JSON'
Carl Backstrom's APEX AJAX Site

Have a clean up, utilising the APEX repository

2007-06-07T23:54:00.001+01:00

Introduction...
Remember switching those conditions to 'Never' on various APEX components and forgotten where and when? Use the awesome APEX repository to have a tidy up.

How...
The following query will return all of the following APEX components that have a condition set to 'Never':
Breadcrumbs, computations, list entries, list of values, navigation bars entries, branches, processes, regions, report columns, validations, buttons, items, tabs and shortcuts.

The query allows for either viewing components by application, or by workspace. To view by application enter the application ID for the app_id bind variable, to view all applications in a workspace just leave app_id as null.

select
  component,
  description,
  sequence,
  location,
  identifier,
  app_id
from
(
  select 
    'Breadcrumb Entry' component,
    entry_label description,
    null sequence,
    'Page: ' || defined_for_page location,
    breadcrumb_id identifier,
    application_id app_id
  from APEX_APPLICATION_BC_ENTRIES
  where upper(condition_type) = 'NEVER'
  union
  select 
    'Application Computation' component,
    computation_item description,
    computation_sequence sequence,
    'Shared Component - Logic' location,
    application_comment_id identifier,
    application_id app_id
  from APEX_APPLICATION_COMPUTATIONS 
  where upper(condition_type) = 'NEVER'
  union
  select 
    'List Entry' component,
    entry_text description,
    display_sequence sequence,
    'Shared Component - Navigation - List (' || 
      list_name || ')' location,
    list_entry_id identifier,
    application_id app_id
  from APEX_APPLICATION_LIST_ENTRIES 
  where upper(condition_type) = 'NEVER'
  union
  select
    'LOV Entry' component,
    display_value description,
    display_sequence sequence,
    'Shared Component - User Interface - LOV (' || 
      list_of_values_name location,
    lov_entry_id identifier,
    application_id app_id
  from APEX_APPLICATION_LOV_ENTRIES 
  where upper(condition_type) = 'NEVER'
  union
  select 
    'Navigation Bar Entry' component,
    icon_subtext description,
    display_sequence sequence,
    'Shared Component - Navigation' location,
    nav_bar_id identifier,
    application_id app_id
  from APEX_APPLICATION_NAV_BAR 
  where upper(condition_type) = 'NEVER'
  union
  select
    'Page Branch' component,
    branch_point description,
    process_sequence sequence,
    'Page: ' || page_id location,
    branch_id identifier,
    application_id app_id
  from APEX_APPLICATION_PAGE_BRANCHES 
  where upper(condition_type) = 'NEVER'
  union
  select
    'Page Computation' component,
    item_name description,
    execution_sequence sequence,
    'Page: ' || page_id location,
    computation_id identifier,
    application_id app_id 
  from APEX_APPLICATION_PAGE_COMP 
  where upper(condition_type) = 'NEVER'
  union
  select 
    'Page Process' component,
    process_name description,
    execution_sequence sequence,
    'Page: ' || page_id location,
    process_id identifier,
    application_id app_id 
  from APEX_APPLICATION_PAGE_PROC 
  where upper(condition_type) = 'NEVER'
  union
  select
    'Page Region' component,
    region_name description,
    display_sequence sequence,
    'Page: ' || page_id location,
    region_id identifier,
    application_id app_id
  from APEX_APPLICATION_PAGE_REGIONS 
  where upper(condition_type) = 'NEVER'
  union
  select
    'Report Column' component,
    column_alias description,
    display_sequence sequence,
    'Page: ' || page_id || ' (' || 
      region_name || ')' location,
    region_report_column_id identifier,
    application_id app_id
  from APEX_APPLICATION_PAGE_RPT_COLS 
  where upper(condition_type) = 'NEVER'
  union
  select
    'Page Validation' component,
    validation_name description,
    validation_sequence sequence,
    'Page: ' || page_id location,
    validation_id identifier,
    application_id app_id 
  from APEX_APPLICATION_PAGE_VAL 
  where upper(condition_type) = 'NEVER'
  union
  select
    'Button' component,
    button_name description,
    button_sequence sequence,
    'Page: ' || page_id || ' (' || 
      region || ')' location,
    button_id identifier,
    application_id app_id
  from APEX_APPLICATION_PAGE_BUTTONS 
  where upper(condition_type) = 'NEVER'
  union
  select
    'Page Item' component,
    item_name description,
    display_sequence sequence,
    'Page: ' || page_id || ' (' || 
      region || ')' location,
    item_id identifier,
    application_id app_id
  from APEX_APPLICATION_PAGE_ITEMS 
  where upper(condition_type) = 'NEVER'
  union
  select
    'Parent Tab' component,
    tab_name description,
    display_sequence sequence,
    'Shared Component - Navigation - Tabs' location,
    parent_tab_id identifier,
    application_id app_id
  from APEX_APPLICATION_PARENT_TABS 
  where upper(condition_type) = 'NEVER'
  union
  select
    'Application Process' component,
    process_name description,
    process_sequence sequence,
    'Shared Component - Logic' location,
    application_process_id identifier,
    application_id app_id
  from APEX_APPLICATION_PROCESSES 
  where upper(condition_type) = 'NEVER'
  union
  select
    'Shortcuts' component,
    shortcut_name description,
    null sequence,
    'Shared Component - User Interface' location,
    shortcut_id identifier,
    application_id app_id
  from APEX_APPLICATION_SHORTCUTS 
  where upper(condition_type) = 'NEVER'
  union
  select
    'Tab' component,
    tab_name description,
    display_sequence sequence,
    'Shared Component - Navigation - Tabs' location,
    tab_id identifier,
    application_id app_id
  from APEX_APPLICATION_TABS 
  where upper(condition_type) = 'NEVER'
)
where app_id = decode(:app_id,null,app_id,:app_id)

The APEX repository views that are being used return different information for the different component types, so I have used generic place holders for the results of an inline view, which does a series of unions on queries retrieving data from the views. I have tried to ensure the what is retrieved for each component type is descriptive enough so as to be able to easily locate the component in your application builder and delete it. A sample result is shown below:

By the way, in writing this post, for an easy and accurate way to get a definitive list of all the apex components that could be conditionally displayed, I went to the 'APEX_DICTIONARY' view and applied the following predicate:

upper(column_name) = 'CONDITION_TYPE'

This returned a list of APEX views that have columns with the name 'CONDITION_TYPE'. These views were then used as a basis for my main query.

Tidy app, tidy mind.

Further information...
Patrick Wolf's excellent post and presentation slides on 'The Power of the Apex Repository'.

Browse Apache files from your browser

2007-06-07T17:25:00.000+01:00

If, like me you have worked on APEX projects with restricted access to the server where the Oracle HTTP Server is installed, there are a few things you can do to view information stored on the server, directly via the browser.

You can look at:

1. Image files
A new feature of APEX 3.0 allows browsing of images that are part of the APEX distribution and available for use, located in following directory:

ORACLE_HOME\Apache\Apache\images\menu

From the 'Application Builder', from the shortcut menu click on the 'Find' torch, pictured below.

This will load a popup, where you can search a whole load of useful APEX stuff, of interest here is the 'Images' tab. Click this, ensure 'Show' is set to 'Standard Images' and you will be able browse the images currently available. By clicking on an image, APEX will also provide you with a link to be used in your application.

Note: This technique can also be used to view cascading style sheets available to you. These CSS shown here are solely the theme independent ones and thus always available to you regardless of theme.

If however, you wish to view a specific CSS file via the browser, read on...

2. CSS and Javascript files
You can look at other files that are contained within the images directory on the server (not just images!), via the virtual path specified in the following configuration file:

ORACLE_HOME\Apache\modplsql\conf\dads.conf

This can be done directly from the browser by removing everything after the port number and replacing it with a path to the file you wish to look at. This would typically be used to look up a '.css' or '.js' file that the current page references, something like:

src="/i/javascript/htmldb_html_elements.js"

Then use this reference at the end of your url, as shown in the picture below.

3. Activity Logs
Although you cannot see the exact access_log files from the browser, there is a report which shows similar information, which can be accessed via:

Home > Utilities > Application Express Views > APEX_WORKSPACE_ACTIVITY_LOG

This contains information like workspace, application, apex_user, page_id, view_date, think_time and ip_address to name a few.

UK market rate for an APEX developer

2007-06-06T11:29:00.003+01:00

For you contractors out there, check out this link to the 'IT JobsWatch' site, where they have some interesting statistics on the current APEX market in the UK for the last 3 months.

When I checked on the 6th June, there were 22 advertisements for contracts in the last 3 months, with 6 of those listing a daily rate averaging £428, it seems the APEX wave is still swelling!

---------------------------
Quarterly Update: 4th September 2007
Adverts in last 3 months: 59
Average Daily Rate: £366 (from 10 adverts quoting a rate)

---------------------------
Quarterly Update (a bit late!!): 1st February 2008
Adverts in last 3 months: 52
Average Daily Rate: £359 (from 14 adverts quoting a rate)

---------------------------
Quarterly Update: 12th April 2008
Adverts in last 3 months: 66
Average Daily Rate: £421 (from 27 adverts quoting a rate)

---------------------------
Quarterly Update: 10th July 2008
Adverts in last 3 months: 60
Average Daily Rate: £442 (from 30 adverts quoting a rate)

---------------------------
Quarterly Update (very late): 12th December 2008
Adverts in last 3 months: 56
Average Daily Rate: £384 (from 14 adverts quoting a rate)