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 

MANAGER
CLERK

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



 

2 comments:

  1. Hi Rowan,

    Your above post is helpful .I am working on a similar work explained in above blog post.However I have a requirement wherein I will will have 'ALL' option in my select list along all employee names.Instead of choosing 1,2,oor 4 employee names I will chooose ALL and press query button.I should see a report for all employees

    For Example contents of select list are

    ALL
    SCOTT
    KING
    BLAKE
    JOHN

    How to do this ? Any help is appreciated

    Thanks,
    Girish

    ReplyDelete
  2. I modified your code to include ALL option as below

    declare
    l_query varchar2(4000);
    where_clause APEX_APPLICATION_GLOBAL.VC_ARR2;
    l_all varchar2(3);
    begin
    l_query:='select ename,empno,job,sal,deptno from emp ';
    where_clause:= APEX_UTIL.STRING_TO_TABLE(:P10_ENAME);

    for i in 1..where_clause.count
    loop
    if i=1 then
    l_all:=where_clause(i);
    if l_all='All' then
    l_query:='select ename,empno,job,sal,deptno from emp ';
    end if;
    l_query:=l_query|| q'[ where ename = ']'||where_clause(i)||q'[']';
    else
    l_query:=l_query|| q'[ or ename = ']' ||where_clause(i)||q'[']';
    end if;
    end loop;

    return l_query;

    end;

    ReplyDelete