Anthony Rayner

Introductionp.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Helvetica; -webkit-text-stroke: #000000} span.s1 {font-kerning: none} p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Helvetica; -webkit-text-stroke: #000000} span.s1 {font-kerning: none}
p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Helvetica; -webkit-text-stroke: #000000} span.s1 {font-kerning: none}

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:

• Materials for "Easy Rich Web, with Application Express and Dynamic Actions"
  
• Materials for "Using Application Express to Build Highly Accessible Products"
  

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.

• 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.

1. If the template is used by an item button, the item name is used, for example 'P1_GO'.
2. 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'.
3. 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'.

    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

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.

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.

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
1. Within an application, click the 'Create Page' button.
2. Select 'Report', click 'Next'.
3. Select 'Interactive Report', click 'Next'.
4. 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'.
5. For 'Tab Attributes', leave as default and click 'Next'.
6. 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)

7. Click 'Finish'.
8. 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'.
If you now click 'Run Page', you'll see the basic report page as been created. We now need to add the page item filters.
• Create the page items
1. 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'.
2. Select 'HTML' and click 'Next'.
3. Select 'HTML' and click 'Next'.
4. 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'.
5. 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.
6. In 'Tree View', right click on the 'Quick Filters' region and select 'Create Page Item'.
7. Select 'Select List', click 'Next'.
8. For 'Item Name', enter 'P1_DEPTNO', click 'Next'.
9. For 'Item Attributes', accept defaults and click 'Next'.
10. For 'Settings', accept defaults and click 'Next'.
11. Ensure 'Display Null Value' is 'Yes'.
12. For 'Null Display Value' enter '- Show All -'.
13. Leave 'Null Return Value' leave as blank, this will be treated as a true null by APEX.
14. For 'List of Values Query', enter the following query and click 'Next':
    
    
    

    select dname d,
    deptno r
    from dept
    order by 1

15. For 'Source' accept defaults and click 'Create Item'.
16. Now for the job select list. In 'Tree View', right click on the 'Quick Filters' region and select 'Create Page Item'.
17. Select 'Select List', click 'Next'.
18. For 'Item Name', enter 'P1_JOB', click 'Next'.
19. 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'.
20. For 'Settings', accept defaults and click 'Next'.
21. Ensure 'Display Null Value' is 'Yes'.
22. For 'Null Display Value' enter '- Show All -'.
23. Leave 'Null Return Value' leave as blank, this will be treated as a true null by APEX.
24. For 'List of Values Query', enter the following query and click 'Next':
    
    
    

    select distinct job d,
    job r
    from emp
    order by 1

25. For 'Source' accept defaults and click 'Create Item'.
If you run the page now, you'll see all the basic page components are created, the regions and the items, but selecting different values from the filters has no effect. We now need to bring these to life.
• Create the dynamic action
1. Go to edit the page. Right click on the 'Dynamic Actions' tree node and select 'Create'.
2. Select 'Advanced' for the 'Implementation'. We need 'Advanced' here because 'Standard' only supports the 'Show', 'Hide', 'Disable' and 'Enable' actions.
3. For 'Name', enter 'QUICK FILTER REFRESH', click 'Next'.
4. 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'.
5. 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.
6. 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'.
7. 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'.
If you now run the page, you'll notice that this still isn't working as expected. Actually, the dynamic action is firing, the report is being refreshed, but it is not being scoped by the filter selection. The problem is the values for the filter page items are not being saved to session state and are therefore not set when the report's SQL is executed. This is easy to fix.
• Define the IRR to save these items values in session state after it is refreshed
1. Right click on the 'Employee Report' and select 'Report Attributes'.
2. 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:

1. 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.
2. 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.

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.

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.

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.

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!

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!

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!!!

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.

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

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

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.

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



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.


Another update...
Please note, if you are working in APEX 4.0 or above, the item type used to display the quick pick links has changed slightly, as part of a wider item consolidation that took place in the 4.0 release. You should use the 'Display Only' item type, with a 'Setting > Based On' attribute equal to 'Output of PL/SQL Code' and then define the call to the QUICK_PICK procedure in the 'PL/SQL Code which emits HTML' attribute. If you have written this in a release prior to 4.0, then when upgrading you are automatically migrated to use these new settings.

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!

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
Choosing the right tool for the right project. This is a massive topic with lots of arguments for and against. For a good overview of the main factors, take a look at Duncan Mills' article, 'The Right Tool For the Right Job'. Also, if you are in the Netherlands, Dimitri will be discussing this with Lucas Jellema on Monday 17th December.

• Validations
Specifically around the current issues with validating tabular form data. Dimitri mentioned Patrick Wolf's ApexLib framework which greatly improves tabular form handling, including out-of-the-box client and server validations for mandatory, date and numeric fields. Alternatively, if you don't want to or cannot use Patrick's framework, you can code your validations referencing the global fxx arrays as mentioned in this rather old, but still useful article. Finally, the Statement of Direction implies that version 4.0 will give us, 'Improved tabular forms, including support for validations...', so looking forward to that.

• URL Tampering
And how this can be combated using the APEX built-in 'Session State Protection', see Dimitri's article or the official documentation.

• Page Comments
Specifically, can these be mandated if this is a development standard? No, not currently but as John suggested this could be easily monitored through the APEX views. The issue also came up that the comments are right down the bottom of the page, which can lead to oversight. This question came up in the forums a couple of weeks ago and Patrick Wolf added a feature to the APEX Builder Plugin which addresses this issue by highlighting the 'Comments' link in yellow if there is a comment. Thank you Patrick!

• Source Control
This keeps on cropping up at the moment. Basically if you are using a source control system such as CVS or SubVersion, what is the best strategy for managing the APEX application files? This can either be done at page level or application level and can be automated through use of the supplied command line tools 'ApexExport' and 'ApexExportSplitter'. APEX development team, how do you manage this?

• JavaScript
A few JavaScript related questions came up. There are lots of libraries available in the APEX release that you can make use as a developer, the functions are unofficially documented by Carl Backstrom and according to the statement of direction will be officially documented and supported from 3.1, which is good news. Also what happens if a user has JavaScript turned off? Well, APEX can be used to build applications that meet accessibility requirements, but it requires some workarounds. See Sergio's article Application Express and Accessibility if you are interested in the steps involved. Peter also added that all client-side validations should be backup up with server-side / APEX validations as best practice.

• Team Development
What is best practice for working on large projects with many developers? We mentioned an excellent article written by Ben Wootton, entitled, 'Best Practices for Oracle Application Express Collaborative Development' as an excellent reference point. This details use of page locks, page groups, use of Application Reports for monitoring, commenting changes, use of PL/SQL functions / procedures rather than embedding logic in page processes and lots more.

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!