Subscribe: Let's talk about APEX, with Anthony Rayner
Added By: Feedage Forager Feedage Grade A rated
Language: English
apex application  apex  application express  application  button  dynamic  express  interactive  new  oracle  page  report 
Rate this Feed
Rate this feedRate this feedRate this feedRate this feedRate this feed
Rate this feed 1 starRate this feed 2 starRate this feed 3 starRate this feed 4 starRate this feed 5 star

Comments (0)

Feed Details and Statistics Feed Statistics
Preview: Let's talk about APEX, with Anthony Rayner

Let's talk about Application Express

Disclaimer: The views expressed on this blog are my own and do not necessarily reflect the views of Oracle.

Updated: 2017-11-10T13:24:50.090+00:00


APEX 5.1 - Client side messaging and apps not using Universal Theme


IntroductionIn 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).The new Reload on Submit page level attribute in APEX 5.1To 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.Compatibility Mode, available in Application AttributesSo 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.Method #1 - Disallowing client side validationThe 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 themeA 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 [...]

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.

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

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!

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.What's Changing?Prior to 4.1, the value substituted for the #BUTTON_ID# substitution string in a button template depended on the type of button:Region buttons substituted the internal numeric ID of the button, for example '123456789101112'.Item buttons actually failed to substitute this value at all, so you would just get the '#BUTTON_ID#' text.We had to address and improve this for the new Dynamic Action / Button integration in 4.1, which means that the value substituted for #BUTTON_ID# will now be one of the following:If the template is used by an item button, the item name is used, for example 'P1_GO'.If the template is used by a region button and a 'Static ID' (new in 4.1) is defined for the button, this is used, for example 'my_custom_id'.If the template is used by a region button and no 'Static ID' is defined, an ID in the format 'B||[Internal Button ID] will be used, for example 'B123456789101112'.The change in behaviour that could be of potential significance in your applications is #3 above. This was changed so as to be a valid HTML 4.01 identifier (begins with a letter) and to be consistent with how we handle other component IDs in Application Express. Will this impact your applications?If you used a button template, that used an ID value substituted by #BUTTON_ID# (you would have had to add it to the button template, as this was never included by default in our themes), and importantly you hard-coded that ID from other places in your code (for example in custom JavaScript to attach behaviour to the button), then this change in behaviour will cause that code to no longer work.What can you do about this?To help you identify region buttons that use a template containing the #BUTTON_ID# substitution string, you can run the following query in your workspace in Application Express 4.0:    select aapb.application_id,           aapb.page_id,           aapb.button_name,           aapb.label,           aapb.button_id,           aapb.button_template,           aatb.template      from apex_application_page_buttons aapb,           apex_applications aa,           apex_application_temp_button aatb     where aapb.application_id     = aa.application_id       and aa.application_id       = aatb.application_id       and aa.theme_number         = aatb.theme_number       and aatb.template_name      = aapb.button_template       and aapb.application_id     = [Your App ID]       and aapb.button_template    is not null       and aapb.button_position    = 'Region Position'       and upper(aatb.template)    like '%#BUTTON_ID#%'     order by 1,2,3Or when APEX 4.1 comes out, you'll be able to do this (by virtue of the addition of the BUTTON_TEMPLATE_ID column to the APEX_APPLICATION_PAGE_BUTTONS dictionary view):    select aapb.application_id,           aapb.page_id,           aapb.button_name,           aapb.label,           aapb.button_id,           aapb.button_template,           aatb.template      from apex_application_page_buttons aapb,           apex_application_temp_bu[...]

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 ValidationHistorically 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 validatedThe 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 validatedPlug-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 ExclusionAgain, 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 u[...]

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.

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: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.commfrom emp e,dept dwhere e.deptno = d.deptnoand 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'.If you now click 'Run Page', you'll see the basic report page as been created. We now need to add the page item filters. Create the page items 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 bl[...]

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


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:Draggable - Define page elements as draggable, with various options such as restricting by vertical or horizontal axis, transparency during drag, containment and more.Droppable - Define page elements as droppable, with various options such as restricting which draggables can be dropped, styling to guide the user where they can drop the element and more.Execute PL/SQL Code - Define a PL/SQL snippet right from within the dynamic action that will be executed on the server, via AJAX. This is currently only coded for Theme 1.Highlight - Patrick Wolf's plug-in that provides the ability to highlight elements on the page.Stripe Report - Used for striping interactive report regions with alternate row colours.I have ensured all the plug-in code is thoroughly commented to try and help you understand exactly what's going on and hopefully get you started in building your own dynamic action plug-ins!! Plug-ins are 1 of the major components of APEX 4.0 and if you're interested to learn more I can thoroughly recommend taking a look at Patrick Wolf's 'How to create a plug-in' blog post and accompanying downloads.I hope you like the application and let me know how you get on!!!Anthony.PS: Many thanks to Patrick Wolf for his invaluable help in reviewing these plug-ins.[...]

Out Now!! Application Express 3.2.1


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

You can get hold of it by either:
  • Downloading the full version from OTN.

  • Download the patch set 8548651 from METALINK.

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

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

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

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


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!

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:
Then the usual post event drinks at a nearby drinkery.

If your interested in coming along then you'll need to register for the event via the website. Pricing varies on the following:
  • If you are already a member of UKOUG then it's free to attend (and you can buy additional places at the members rate of £80 plus VAT for colleagues).
  • If you are not a member then they have setup a non-member rate of £160 plus VAT.
There are only 55 places available so attendance is limited.

Hopefully see you there!

'Publish to APEX' from SQL Developer 1.5.3


Since SQL Developer 1.2.1 and APEX 3.0.1, we've had some useful integration between APEX and SQL Developer, the ability to import and deploy applications, browse the APEX metadata, remote debug PL/SQL in your applications and more. With the latest release of SQL Developer 1.5.3, it is now possible to create a quick and simple APEX application from within SQL Developer (thanks to a bug fix). This is possible through the 'Publish to APEX' feature and creates a simple 1 page APEX application containing an Interactive Report Region based upon a SQL statement.(Note: Requires APEX 3.1 or above.)This feature allows you to take any grid of data, right click on it and select to 'Publish to APEX'. (Note a 'grid of data' includes results from executing a SQL statement, results of pre-defined or user-defined reports from the 'Reports' tab, specific table / view properties such as columns, data, constraints etc. and I'm sure there are more.) Upon selecting 'Publish to APEX', the following dialog is displayed:This dialog allows you to specify 5 properties:1) Workspaces- The workspace where you want the application to be created (this list will only display workspaces that are associated with the schema of the context of your current grid of data).2) Application Name - The name of the application that will be created.3) Theme - The theme for your new application, specifying look and feel.4) Page Name - The name of the page that will be created.5) SQL - The SQL that will be used to generate an interactive report region within the page. This defaults to the SQL used to build the original grid of data, but can be changed.Upon clicking 'Apply' SQL Developer will create the application and show a dialog with some basic creation information such as application name and ID. This creates a 1 page application in the workspace defined, containing an interactive report region with a source of the SQL specified in the dialog. A few more points to note about this application:- The application's authentication scheme defaults to 'Database Account Credentials' meaning that you'll need to authenticate into the application using valid database account username and password. This can obviously be changed to something else if required.- The application will be created in an application group called 'Published from SQL Developer'.- The interactive report region only displays the first ten columns of the report by default, but again this can easily be changed via the interactive reports menu bar 'Actions' green cog drop down, then select 'Select Columns'.Here is an example of the application that is generated. I selected to publish data that showed access attempts to my workspace (selecting from the APEX metadata view apex_workspace_access_log). I then used interactive report features to refine my data to show all the failed login attempts for applications within a workspace, grouped by application:Have fun publishing to APEX!!![...]

Out Now!! Application Express 3.1.2


APEX 3.1.2 is now available for download and fixes the following bugs. You can get hold of it by either:
  • Downloading the full version from OTN.

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

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

Happy patching!

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!

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...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 1Use the 'Is in the last' operator, and then specify some arbitrarily large amount (100 years).This could then be saved by the user as a 'Named Report' for future use. Although this is the quickest and easiest method, it isn't very intuitive for the user to come up with and also it's not 100% water tight to use this filter, as it might be possible that a date is entered outside of the arbitrary range specified.Method 2It'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.- Untick the 'Show' checkbox for the SYSDATE column.- Click 'None' for 'Headings Type'.3) Add a column link to the report by going the 'Report Attributes' page and clicking the 'Add Column Link' link from the 'Tasks' menu on the right. For this column link, specify the following values:- Link Text: 'Overdue Reviews'- Page: 2- Clear Cache: '2,CIR'- Item 1 Name: IRLT_REVIEW_DATE- Item 1 Value: #SYSDATE#Note: My interactive report is on page 2 of my application, but change all occurences of 2 to to whatever your page is.This generates a link like this:Of particular interest here are...Clear Cache = '2,CIR'This clears the cache for page 2, and also uses the new 'CIR' parameter, which clears all settings that could already have been applied to the interactive report (including any default report settings).Item Names = 'IRLT_REVIEW_DATE'This can be broken down as follows: 'IR': This is used to set interactive report filters dynamically over the URL. 'LT': The characters directly after the 'IR' prefix are equivalent to the 'Operator' value in the interactive report 'Filter' dialog, so in this case 'LT' specifies a 'Less than' operator. Note: You could also use 'LTE' which would specify a 'Less than or equal to' operator. See here for more options. 'REVIEW_DATE': Then after the underscore, this is the column name on which to apply the filter, equivalent to the 'Column' value in the interactive report 'Filter' dialog. In this case we are filtering on the 'REVIEW_DATE' column.This method works well, but does require some sort of separate menu or link to go to the interactive report, which steps out of the normal use-case for interactive reports.Method 3The fin[...]

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.


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: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 replacePROCEDURE 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('
'); 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('
'); HTP.P('
');END QUICK_PICK;So this procedure takes a SQL query (p_query) and a page item ID (p_item) as parameters. The results from the query specified in p_query will be used as the display and setting values of each quick pick link, so the query must contain two columns in the select statement (the first selected column should be the display value to be used and the second the return value, see query below for an example). The item name specified in p_item will be the item that the quick pick link will set.This procedure just opens a dynamic cursor based on the query specified in the p_query parameter. Then calls the HTP.ANCHOR function to generate the anchor HTML tag. The procedure also pads the lot in a div tag and adds a line break at the end for presentation (you may want to change this if you have another page item on the same line as the select list being set). The following is an example of the HTML generated by this procedure: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 LabelSource > Source Type: PL/SQL Anonymous BlockBEGIN QUICK_PICK('SELECT summed_rows.job display , summed_rows.job return FROM (SELECT job, SUM(1) qty FROM emp [...]

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!

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:APEX / Forms / ADFChoosing 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.ValidationsSpecifically 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 TamperingAnd how this can be combated using the APEX built-in 'Session State Protection', see Dimitri's article or the official documentation.Page CommentsSpecifically, 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 ControlThis 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 [...]

UKOUG - Day 2


It had finally arrived, Tuesday 4th December. A day that had been engrained on my brain ever since receiving 'UKOUG 2007 - Speaker Confirmation' in my Inbox back in July. The day of my first big presentation, 'Building The Rich User Interface with Oracle Application Express and Ajax'. So very nervous I headed down to Birmingham from Reading and arrived at the ICC at around 9.30am. I was speaking at 11.15 so gave myself a bit of time to register and go over the slides one last time. A worthwhile activity as I was soon to realise on discovering 3 of my slides were missing which I must have deleted the previous night. Thank goodness for backups!! I headed up to Hall 8b, the venue for my presentation. It was a good size, around 170 capacity but didn't seem to big so I was ok with that. There was no session in there before me so had plenty of time to get setup and settled, or so I thought. The technician arrived soon after me and asked if I was going to be using the supplied laptop or my own, I said my own and he worringly replied, 'You are the first person who's wanted to use their own'. I replied 'Is that a problem?', and he assured it wasn't but there would just be a bit of setup to get it working. Time went by, people started arriving and my opening slide was still no where to be seen on the big wall behind me. The room of around 50 people was filled with silence, Harrison my session chair did a good job of trying to break it with a quick poll of how many people had used APEX before, which was most (but not all) of the room. I then ran through my agenda which seemed like it took about 5 minutes but probably only lasted about 30 seconds. Anyway, with numerous trips back and forth to the control room by the technician, my opening slide finally appeared on the big wall, a sigh of relief from me and after all the waiting and anticipation, I could talk.It went ok. I know there are a few things I could have explained better and in more detail. I had always been worried about the amount of technical information I was trying to get across in 1 hour (although originally I was worried I wouldn't be able to fill it!) and in hindsight, I think I should have gone for longer. So having finished talking in about the right time, there were a few questions and the job was done. John Scott came up to me afterwards and introduced himself. It was great to finally meet him having only known of him before. He said my presentation was really good, which means a lot coming from someone like John and also suggested that I should have a longer time slot to get through everything, I definitely agree.Me, full of relief!For anyone who is interested in getting the slides I have currently taken them offline just because I wanted to change a few small things. Once these changes are made I will post a link on my blog. I am also trying to get a demo up and running which will have all the code available to download. Watch this space! Having done my presentation, and with the weight of an elephant lifted from my shoulders I was then free to enjoy some of the conference. Really enjoyed Zory Zeharieva of CERN present, 'A Real-Life Experience of Rapidly Building Web-Interfaces with Oracle Application Express at CERN'. She presented well and covered some best practices for building scalable applications which was very interesting and also expressed some concerns around the way APEX application files are managed within versioning systems, a common concern which came up in a few places over the conference. Also then dropped in[...]

Problem with 'Not Null' validations


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

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

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

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

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

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


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


Hope it helps,

Debugging with the APEX repository


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

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

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

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

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

We can then run the following query to return all the items for a specific application / page bound to a db column more than once.
SELECT   db_column_name,
SUM(1) Duplicates
FROM apex_application_page_db_items
WHERE page_id = :page_id
AND application_id = :app_id
GROUP BY db_column_name
(Note: This view does not contain conditional rendering information, so if this was required you would need to join to APEX_APPLICATION_PAGE_ITEMS on ITEM_ID).

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

Error: ORA-00957 - Duplicate column name
  • Step 1: Run the following query, binding in your page and application id.
    SELECT   db_column_name,
    SUM(1) Duplicates
    FROM apex_application_page_db_items
    WHERE page_id = :page_id
    AND application_id = :app_id
    GROUP BY db_column_name
    HAVING SUM(1) > 1

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

  • Step 3: Retest your page.

That would be nice.

Survey results


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

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

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

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

What do you want to learn about?


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

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

Look forward to hearing from you!