Thursday, 23 February 2012

Oracle APEX - Filtering data from multi-select LOV types eg multi-select lists, radio buttons and shuttles – using PL/SQL

During one of my recent training courses a delegate asked how you deal with data coming from items that hold multiple values such as multi-select lists, shuttles and radio buttons. So I thought I would try and explain things via my blog. 

First of all it’s important to understand that these datatypes don’t actually return multiple values they are actually returning a single value that is in a particular format ie ‘Value1:Value2’ where Value1 and 2 relate to your data eg jobs ie ‘MANAGER:CLERK’. The main crux of the issue here is how to split up this string into its component parts ie 


For this we can use the APEX_UTIL. STRING_TO_TABLE(:P10_JOBS_FILTER) function to take in the item values (in the format ‘ANALYST:MANAGER’) and return them into a variable based on APEX_APPLICATION_GLOBAL.VC_ARR2 ie an associative array with a varchar2 index.  So let’s see a working example.

First of all create a blank page with a classic report on it. Use the following for the source code of the report :


Next create a Multi-Select item called P10_JOBS_FILTER with the following in the List of Values section:


And add a Go button to submit the page – so your page should look a bit like this:


Of course you can avoid all the PL/SQL and just use the following SQL as the report source code:

But no way near as much fun or future proof – and definitely not enough to write a blog on smile emoticon


No comments:

Post a Comment