Well, it’s been a quite while since I posted (I know, I’m slacking) so I thought I’d just quickly blog about what I just helped someone with yesterday. It required me to use a pipelined function. Not everyone has seen of them so I thought it may be interesting.
Pipelined functions allow you to basically select from a function rather than a database table.
A colleague of mine had to build an APEX report of opportunities. The report was to be constrained by a multiselect listbox of product ids. Show all opportunities that have ANY of the selected product ids. So far it sound pretty easy. The tricky part was how the product ids were stored in the opportunity table.
create table opportunity( name varchar2(100), product_ids varchar2(255) );
The PRODUCT_IDS column was populated with a colon delimited list of all the products that were used in that opportunity. Now before you yell that that is probably not the best way to store that data and that a master-detail table relationship is probably the better way to go, I 100% agree. But unfortunately, when you inherit an application, you many times must make do with what the original developer put in place (and that is the case here).
We all know when you submit a multiselect listbox in APEX, it comes in as a colon delimited list of all the values that were selected. What we need to do is tokenize that selectlist string and compare each value against the table.
I came up with two possible solutions:
1. Build the query by hand.
Using apex_util.string_to_table() on the input, loop over all the values and manually build the report query by hand.
declare l_ids apex_application_global.vc_arr2; l_query varchar2(32767) := null; begin l_ids := apex_util.string_to_table( :p9_product_ids ); for i in 1 .. l_ids.count loop l_query := l_query || <insert query here>; if i <> l_ids.count then l_query := l_query || ' union all '; end if'; end loop; return l_query; end;
This would have worked just fine (besides having to run the query N times and union all them together).
But I like to use nifty, (and more optimized) options and so I implemented my other solution:
2. Use a pipelined function.
To use this, we need to create a type and a function that returns that type.
create or replace type myTableType as table of varchar2(255) /
create or replace function to_myTableType( p_string varchar2 ) return myTableType PIPELINED is l_arr apex_application_global.vc_arr2; begin l_arr := apex_util.string_to_table( p_string ); for i in 1 .. l_arr.count loop PIPE ROW( ':' || l_arr(i) || ':' ); end loop; return; end; /
Notice that the function is declared as PIPELINED in its declaration. Also notice the PIPE ROW() syntax in the loop. That is where the function returns multiple values as rows in a query. Its using the string_to_table() function to tokenize the string and then looping over the tokens, pipes the results out as rows. And finally, this function contains a return clause without returning anything. It had already returned multiple values in the PIPE ROW() line.
Now that we have this we can write a single, simple query in APEX that looks like this:
select o.name, o.product_ids from opportunity o, table( to_myTableType( :p1_product_ids )) p where instr( ':' || o.product_ids || ':', p.COLUMN_VALUE ) > 0;
Check out a working version here: http://apex.oracle.com/pls/otn/f?p=19903:9