Anthony Rayner
![Subscribe to Anthony Rayner feed](http://www.orafaq.com/misc/feed.png)
Disclaimer: The views expressed on this blog are my own and do not necessarily reflect the views of Oracle.Anthony Raynerhttp://www.blogger.com/profile/04412495232341508437noreply@blogger.comBlogger34125
Updated: 14 hours 59 min ago
APEX 5.1 - Client side messaging and apps not using Universal Theme
Introduction
In APEX 5.1, we are introducing new client side messaging functionality, which aims to provide more modern, faster messaging across APEX, including the capability to display both error and success messages from the client side, without requiring a full page reload (as is the traditional way in APEX). Messaging in APEX is something we have wanted to modernise for a long time, and in 5.1, precipitated by the introduction of the Interactive Grid and its requirement for such messaging, this comes to fruition.
This new functionality works well with apps already using the Universal Theme, however apps using older, or custom themes may face some consistency issues in how inline item errors are displayed between pages that display messages the old way and the new way. The purpose of this blog post is to highlight when such inconsistencies may occur, and to provide guidance on how you can go about fixing them.
Note: If you have created your own custom theme, please see Method #2 for instructions on how to update your theme to work with client side messaging.
When can these inconsistencies occur?
Client side messaging for inline item errors will be used in the following scenarios:
Compatibility Mode, available in Application Attributes
So as soon as you start adding new pages to an existing app for example, you will start to hit these inconsistencies, because existing pages will have Reload on Submit set to Always, and new pages will have this set to Only for Success.
In both of the above scenarios, an inline item error will be shown but it may not look exactly the same as on other existing pages. This is because as it is, the client side messaging logic isn't able to use the corresponding template markup from the theme because of some missing information (more on that in a moment), and instead relies on some generic fallback logic to display the error. We can see the inconsistency below.
Here is an inline item error, rendered using server-side logic due to the page level attribute Reload on Submit being set to Always (default for existing apps).
And here is the same error, rendered on the client side due to the page level attribute Reload on Submit being set to Only for Success (default for new pages).
Method #1 - Disallowing client side validation
The first solution is described in the Known Issues as follows:These differences can be avoided by setting Reload on Submit to Always, (please note the Interactive Grid feature requires this to be set to Only for Success and not using client side validation by either setting app Compatibility Mode to less than 5.1, or setting Execute Validations = No.Setting Reload on Submit to Always, or disallowing client side validation as described will of course resolve the inconsistencies, but are somewhat brute-force, with the negative side-effects that you cannot benefit from the new way, you are precluded from upgrading your compatibility mode and also by setting Reload on Submit to Always, this means you cannot use the Interactive Grid on that page.
This is good for a quick-fix, but does place limitations upon your app, which are far from ideal.
Method #2 - Updating your theme
A better way to resolve this, and one that doesn't limit you in the same way as detailed in Method #1, is to update your themes so that they work better with the new client side messaging. Specifically this involves an update to your theme's label templates.
The reason that the client side messaging functionality is not able to use the markup from the label template, is because it relies on the following:
Theme 21 > Optional Label with Help label template before any changes. Shows an empty Error Template and error markup defined in the On Error Before Label and On Error After Label attributes
Given the aforementioned rules, this clearly won't work, there is no Error Template defined, so the client side will need to resort to its fallback template, causing the possible inconsistencies.
So firstly, we need to move the error related markup into the Error Template attribute. However, this presents a slight problem, because the way it is currently, the On Error Before Label markup will be rendered exactly as described, before the label markup when an error occurs, and the On Error After Label markup similarly will be rendered after the label markup. This means that it will effectively wrap the label markup in the template, which is very difficult to replicate by switching to the Error Template approach (where the markup is defined in its entirety in a single place, and inserted into the template in a single place). Unfortunately this means we will need to compromise, and go with an approach that will be slightly different to how it looked prior to 5.1, however importantly this will be consistent between client side and server-side rendered messages now, and without the downsides of having to resort to Method #1.
So we move the combined error markup into the Error Template, as shown:
Theme 21 > Optional Label with Help label template after moving the error markup into the Error Template attribute
Note: We removed the line break because we no longer need to force a new line after the label as was the case before, and because the DIV will automatically start on a new line because it is a block-level element, so the error will appear on a new line after its preceding element.
The next part is to add the #ERROR_TEMPLATE# substitution string to the best place in either of the two attributes where it is supported in a label template, either the Before Item or After Item attributes. Let's go with the more common approach of displaying the error after the item, so simply add #ERROR_TEMPLATE# in the After Item attribute, as shown:
You will need to replicate this in the different label templates in your app, and of course different themes will have slightly different implementations for label templates, but if you follow the rules laid out above, then it should be a fairly straight forward switch over.
Updated appearance following template change, now consistent irrespective messaging type
Method #3 - Migrate to Universal Theme
This will almost certainly result in the most work, but will provide great benefit in the long run, and all these problems will automatically go away.
Universal Theme Sample Application, available in your workspace via Packaged Apps > Sample
This will fix these inconsistency issues, and would be the best thing you can do to also benefit from the huge improvements offered by Universal Theme over our legacy themes, but will most likely take the most time and should be considered as part of a migration project (rather than a simple change), so may of course not be feasible. To see our Migration Guide, specifically the section entitled Migrating from Other Themes for further information.
Summary
The new client side messaging of APEX 5.1 offers some great benefits over the server-side approach, but does represent some issues with inline errors and apps not using Universal Theme as detailed here. If you have time, converting to Universal Theme would definitely be the way to go, there are just such massive improvements to benefit from in terms of capability, accessibility, responsiveness, modern look and feel, and more, but this will of course take the most time and investment. If this is not feasible, we would recommend updating your existing themes as detailed here, to be able to provide a consistent look and feel, and still benefit from other new features in 5.1 as described.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}
p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Helvetica; -webkit-text-stroke: #000000} span.s1 {font-kerning: none}
In APEX 5.1, we are introducing new client side messaging functionality, which aims to provide more modern, faster messaging across APEX, including the capability to display both error and success messages from the client side, without requiring a full page reload (as is the traditional way in APEX). Messaging in APEX is something we have wanted to modernise for a long time, and in 5.1, precipitated by the introduction of the Interactive Grid and its requirement for such messaging, this comes to fruition.
This new functionality works well with apps already using the Universal Theme, however apps using older, or custom themes may face some consistency issues in how inline item errors are displayed between pages that display messages the old way and the new way. The purpose of this blog post is to highlight when such inconsistencies may occur, and to provide guidance on how you can go about fixing them.
Note: If you have created your own custom theme, please see Method #2 for instructions on how to update your theme to work with client side messaging.
When can these inconsistencies occur?
Client side messaging for inline item errors will be used in the following scenarios:
- To display the error messages if an APEX validation error occurs, and the page attribute Reload on Submit is set to Only for Success (which is the default for new pages, irrespective of theme).
- To display the error messages if a client side validation error occurs such as Value Required = Yes, and the app Compatibility Mode is set to 5.1 (default for new apps, irrespective of theme), a Submit Page button is used with set to Execute Validations = Yes.
![Screenshot showing the new Reload on Submit page level attribute in APEX 5.1](https://1.bp.blogspot.com/-2WwXbRtpiLo/WFuOq85pIoI/AAAAAAAAAYk/uyewF97j0isMq8Ws55IUTavW_yiBpzGKACLcB/s1600/Screen%2BShot%2B2016-12-22%2Bat%2B08.28.00.png)
![](https://3.bp.blogspot.com/-Bq5OoA-3eSc/WFuOHtIG_EI/AAAAAAAAAYg/LJShdSKomIksoFBGIJnkzOXueOYxG4FtACLcB/s1600/Screen%2BShot%2B2016-12-22%2Bat%2B08.25.48.png)
So as soon as you start adding new pages to an existing app for example, you will start to hit these inconsistencies, because existing pages will have Reload on Submit set to Always, and new pages will have this set to Only for Success.
In both of the above scenarios, an inline item error will be shown but it may not look exactly the same as on other existing pages. This is because as it is, the client side messaging logic isn't able to use the corresponding template markup from the theme because of some missing information (more on that in a moment), and instead relies on some generic fallback logic to display the error. We can see the inconsistency below.
Here is an inline item error, rendered using server-side logic due to the page level attribute Reload on Submit being set to Always (default for existing apps).
And here is the same error, rendered on the client side due to the page level attribute Reload on Submit being set to Only for Success (default for new pages).
The Known Issues page for 5.1 mention this issue, including a few suggestions for working around it.
Now let's explore each of the suggestions in a bit more detail.
The first solution is described in the Known Issues as follows:
This is good for a quick-fix, but does place limitations upon your app, which are far from ideal.
Method #2 - Updating your theme
A better way to resolve this, and one that doesn't limit you in the same way as detailed in Method #1, is to update your themes so that they work better with the new client side messaging. Specifically this involves an update to your theme's label templates.
The reason that the client side messaging functionality is not able to use the markup from the label template, is because it relies on the following:
- The error markup being provided in the Error Template attribute of the label template.
- The #ERROR_TEMPLATE# substitution string being included in either the Before Item or After Item attribute of the label template.
Now we know what's required, let's look at an example using the standard Theme 21, and the Optional Label with Help label template. In this template, you will see the Error Display information is provided as follows:
![Screenshot showing Theme 21 > Optional Label with Help label template before any changes. Shows an empty Error Template and error markup defined in the On Error Before Label and On Error After Label attributes](https://1.bp.blogspot.com/-3WGXojojNLY/WFuSKeCkE3I/AAAAAAAAAZA/TFONo5Ze8UcTGtjyjePzzifYxmWGkuLZACLcB/s1600/Screen%2BShot%2B2016-12-22%2Bat%2B08.43.05.png)
Given the aforementioned rules, this clearly won't work, there is no Error Template defined, so the client side will need to resort to its fallback template, causing the possible inconsistencies.
So firstly, we need to move the error related markup into the Error Template attribute. However, this presents a slight problem, because the way it is currently, the On Error Before Label markup will be rendered exactly as described, before the label markup when an error occurs, and the On Error After Label markup similarly will be rendered after the label markup. This means that it will effectively wrap the label markup in the template, which is very difficult to replicate by switching to the Error Template approach (where the markup is defined in its entirety in a single place, and inserted into the template in a single place). Unfortunately this means we will need to compromise, and go with an approach that will be slightly different to how it looked prior to 5.1, however importantly this will be consistent between client side and server-side rendered messages now, and without the downsides of having to resort to Method #1.
So we move the combined error markup into the Error Template, as shown:
![Screenshot showing Theme 21 > Optional Label with Help label template after moving the error markup into the Error Template attribute](https://4.bp.blogspot.com/-5sW9pOwiKTk/WFuUhEf0TfI/AAAAAAAAAZQ/mOoUD56fu30fsBYGHEgTMBfuvUrSdySfQCLcB/s1600/Screen%2BShot%2B2016-12-22%2Bat%2B08.53.05.png)
The next part is to add the #ERROR_TEMPLATE# substitution string to the best place in either of the two attributes where it is supported in a label template, either the Before Item or After Item attributes. Let's go with the more common approach of displaying the error after the item, so simply add #ERROR_TEMPLATE# in the After Item attribute, as shown:
You will need to replicate this in the different label templates in your app, and of course different themes will have slightly different implementations for label templates, but if you follow the rules laid out above, then it should be a fairly straight forward switch over.
![Screenshot showing Updated appearance following template change with the error displayed below the input, now consistent irrespective messaging type](https://1.bp.blogspot.com/-xWdPruiV3Ck/WFuV8aT5fkI/AAAAAAAAAZc/uw9Zwesvgvk42KDV9FuoULxUZZhkshYwgCLcB/s400/Screen%2BShot%2B2016-12-22%2Bat%2B08.59.04.png)
This will almost certainly result in the most work, but will provide great benefit in the long run, and all these problems will automatically go away.
![Screenshot showing Universal Theme Sample Application, available in your workspace via Packaged Apps > Sample](https://2.bp.blogspot.com/-PQDZkeb2wGw/WFuWx48j9RI/AAAAAAAAAZk/viwdCz8Be-EqZJ5ruoeccaEE6YRQHlaCQCLcB/s640/Screen%2BShot%2B2016-12-22%2Bat%2B09.02.27.png)
This will fix these inconsistency issues, and would be the best thing you can do to also benefit from the huge improvements offered by Universal Theme over our legacy themes, but will most likely take the most time and should be considered as part of a migration project (rather than a simple change), so may of course not be feasible. To see our Migration Guide, specifically the section entitled Migrating from Other Themes for further information.
Summary
The new client side messaging of APEX 5.1 offers some great benefits over the server-side approach, but does represent some issues with inline errors and apps not using Universal Theme as detailed here. If you have time, converting to Universal Theme would definitely be the way to go, there are just such massive improvements to benefit from in terms of capability, accessibility, responsiveness, modern look and feel, and more, but this will of course take the most time and investment. If this is not feasible, we would recommend updating your existing themes as detailed here, to be able to provide a consistent look and feel, and still benefit from other new features in 5.1 as described.
p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Helvetica; -webkit-text-stroke: #000000} span.s1 {font-kerning: none}
Categories: Development
UKOUG TECHEBS 2011 Presentations
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.
- Materials for "Easy Rich Web, with Application Express and Dynamic Actions"
- Materials for "Using Application Express to Build Highly Accessible Products"
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.
Categories: Development
Oracle OpenWorld 2011 Dynamic Action Presentation
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!
Categories: Development
Application Express 4.1 - #BUTTON_ID# Changed Behaviour
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.
Prior to 4.1, the value substituted for the #BUTTON_ID# substitution string in a button template depended on the type of button:
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?
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
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
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:
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):
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.
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.
Categories: Development
APEX 4.0 Enhancements: Validating Form Data
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:
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.
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.
![](http://2.bp.blogspot.com/_0W0wsNIv6jU/TFrQudMEcLI/AAAAAAAAAKw/ifqyq2afLkI/s400/number_settings.jpg)
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.
![](http://1.bp.blogspot.com/_0W0wsNIv6jU/TFrPqujSmFI/AAAAAAAAAKo/LL1CvBwySNI/s400/date_settings2.jpg)
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.
![](http://3.bp.blogspot.com/_0W0wsNIv6jU/TFq9unHkQeI/AAAAAAAAAKY/bOwVD2QPnb0/s400/button_exclusion2.jpg)
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.
![](http://3.bp.blogspot.com/_0W0wsNIv6jU/TFq8vrKkSnI/AAAAAAAAAKI/L0q9GkM5PDs/s400/error_message_small2.jpg)
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.
Categories: Development
APEX 4.0 - Do you want to know more?
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.
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.
Categories: Development
Report filtering with APEX 4.0, dynamic action style
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:
Now let's walk through these steps in detail.
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:
To learn more about these examples, please download the application, install in your workspace and have a closer look.
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.
Categories: Development
APEX 4.0 - New Dynamic Action Sample Application
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.
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:
I welcome any feedback you may have and will try and blog some more about some of the specific examples when I can.
Anthony.
- 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.
Categories: Development
APEX 4.0 - Learn more about Dynamic Actions
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:
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.
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.
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.
Categories: Development
Out Now!! Application Express 3.2.1
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: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.
You can get hold of it by either: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.
Categories: Development
OTNs APEX Developer Competition 2009
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!
...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!
Categories: Development
APEX SIG in the UK - Finally!
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:
If your interested in coming along then you'll need to register for the event via the website. Pricing varies on the following:
Hopefully see you there!
- 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
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.
Hopefully see you there!
Categories: Development
'Publish to APEX' from SQL Developer 1.5.3
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.
![](http://1.bp.blogspot.com/_0W0wsNIv6jU/SUJSQP0cEQI/AAAAAAAAAHM/xwCmDs0SU8I/s400/12-12-2008+11-39-59.gif)
(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:
![](http://4.bp.blogspot.com/_0W0wsNIv6jU/SUJRzmrNU9I/AAAAAAAAAHE/hdRr22WPrGo/s400/12-12-2008+11-41-43.gif)
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.
![](http://3.bp.blogspot.com/_0W0wsNIv6jU/SUJUtk_0F1I/AAAAAAAAAHU/LK0ovZz7tWg/s400/12-12-2008+11-42-48.gif)
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:
![](http://3.bp.blogspot.com/_0W0wsNIv6jU/SUJXt31X-9I/AAAAAAAAAH0/9m10BCRlEzw/s400/12-12-2008+12-22-55.gif)
Have fun publishing to APEX!!!
![](http://1.bp.blogspot.com/_0W0wsNIv6jU/SUJSQP0cEQI/AAAAAAAAAHM/xwCmDs0SU8I/s400/12-12-2008+11-39-59.gif)
(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:
![](http://4.bp.blogspot.com/_0W0wsNIv6jU/SUJRzmrNU9I/AAAAAAAAAHE/hdRr22WPrGo/s400/12-12-2008+11-41-43.gif)
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.
![](http://3.bp.blogspot.com/_0W0wsNIv6jU/SUJUtk_0F1I/AAAAAAAAAHU/LK0ovZz7tWg/s400/12-12-2008+11-42-48.gif)
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:
![](http://3.bp.blogspot.com/_0W0wsNIv6jU/SUJXt31X-9I/AAAAAAAAAH0/9m10BCRlEzw/s400/12-12-2008+12-22-55.gif)
Have fun publishing to APEX!!!
Categories: Development
Out Now!! Application Express 3.1.2
APEX 3.1.2 is now available for download and fixes the following bugs. You can get hold of it by either:
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.
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.
Categories: Development
APEX with eBusiness Suite survey reminder
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
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
Categories: Development
Dynamic Date Filtering in Interactive Reports
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...
![](http://bp3.blogger.com/_0W0wsNIv6jU/SH4sH0CUI4I/AAAAAAAAAE0/XNb9gYRMI8M/s400/filter_not_allowed.gif)
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).
![](http://bp1.blogger.com/_0W0wsNIv6jU/SH3f_hIErSI/AAAAAAAAADc/2QC1k-l12Gs/s400/is_in_the_last.gif)
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:
This generates a link like this:
![](http://bp3.blogger.com/_0W0wsNIv6jU/SH30P0flDrI/AAAAAAAAAEc/EKZgNxiC3DI/s400/dynamic_url.gif)
Of particular interest here are...
![](http://bp1.blogger.com/_0W0wsNIv6jU/SH3y2mYMd_I/AAAAAAAAAEM/BfkgOuZAzIM/s400/irr_dynamic_via_url.gif)
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:
![](http://bp3.blogger.com/_0W0wsNIv6jU/SH4esFWXqQI/AAAAAAAAAEs/Z3YcAXK0scA/s400/derived_filter.gif)
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...
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
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...
![](http://bp3.blogger.com/_0W0wsNIv6jU/SH4sH0CUI4I/AAAAAAAAAE0/XNb9gYRMI8M/s400/filter_not_allowed.gif)
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).
![](http://bp1.blogger.com/_0W0wsNIv6jU/SH3f_hIErSI/AAAAAAAAADc/2QC1k-l12Gs/s400/is_in_the_last.gif)
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 dual2) Ensure the following is set during the wizard creation:
- Report Template: template: [theme number].Borderless3) Go the 'Report Attributes' page and specify the following:
- Pagination Scheme: No pagination.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:
- Untick the 'Show' checkbox for the SYSDATE column.
- Click 'None' for 'Headings Type'.
- Link Text: 'Overdue Reviews'Note: My interactive report is on page 2 of my application, but change all occurences of 2 to to whatever your page is.
- Page: 2
- Clear Cache: '2,CIR'
- Item 1 Name: IRLT_REVIEW_DATE
- Item 1 Value: #SYSDATE#
This generates a link like this:
![](http://bp3.blogger.com/_0W0wsNIv6jU/SH30P0flDrI/AAAAAAAAAEc/EKZgNxiC3DI/s400/dynamic_url.gif)
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.
![](http://bp1.blogger.com/_0W0wsNIv6jU/SH3y2mYMd_I/AAAAAAAAAEM/BfkgOuZAzIM/s400/irr_dynamic_via_url.gif)
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:
(caseOnce this has been done by the developer, the user could then choose to apply a filter on this column as shown in the screenshot:
when review_date < sysdate then 'Yes'
else
'No'
end) review_overdue
![](http://bp3.blogger.com/_0W0wsNIv6jU/SH4esFWXqQI/AAAAAAAAAEs/Z3YcAXK0scA/s400/derived_filter.gif)
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
Categories: Development
New job...
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.
Anthony.
Categories: Development
Dynamic Quick Picks - APEX style!
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:
![](http://bp0.blogger.com/_0W0wsNIv6jU/R69NPjwhY5I/AAAAAAAAACs/l1Df07yoqFE/s400/apex_quick_pick.jpg)
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:
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:
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
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:
The results can be seen here:
![](http://bp3.blogger.com/_0W0wsNIv6jU/R69NeTwhY6I/AAAAAAAAAC0/Y49ayBxnpcI/s400/custom_quick_pick.jpg)
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.
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:
![](http://bp0.blogger.com/_0W0wsNIv6jU/R69NPjwhY5I/AAAAAAAAACs/l1Df07yoqFE/s400/apex_quick_pick.jpg)
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 replaceSo 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.
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;
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
BEGINThis 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.
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;
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 {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:
color:#811919;
font-size:11px;
margin:0pt 5px 5px 0pt;
text-decoration:none;
white-space:nowrap;
}
<link rel="stylesheet" href="#WORKSPACE_IMAGES#custom.css"
type="text/css" />
The results can be seen here:
![](http://bp3.blogger.com/_0W0wsNIv6jU/R69NeTwhY6I/AAAAAAAAAC0/Y49ayBxnpcI/s400/custom_quick_pick.jpg)
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.
Categories: Development
UKOUG - My presentation slides are now available...
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!
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!
Categories: Development
UKOUG - Day 3
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:
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!
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.
![](http://bp0.blogger.com/_0W0wsNIv6jU/R116G4o-0CI/AAAAAAAAACc/I5DAlakE5J4/s400/DSC00261.jpg)
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!
Categories: Development