Home > APEX, Oracle, PL/SQL > Pipelined Functions

Pipelined Functions

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

About these ads
Categories: APEX, Oracle, PL/SQL Tags: , ,
  1. Stew Stryker
    December 6, 2008 at 12:45 pm | #1

    Chris,

    Nice job. Though I looked at your final query and at first though you hadn’t joined your two tables! It took me a second to recognize the instr() was going that! Duh…

    I’ve used pipelined functions a few times too and they fit some special needs well.

    Actually I used them quite a bit on my first Apex application (when I didn’t know better). I had 2 data sets I needed from our main production schema and I wanted to be sure it was secure from web hackers trying to get through my Apex application. I first tried database views and the underlying tables complained that they didn’t have the GRANT option set up for me. So I created 2 pipelined functions. It worked fine, though I found later that views would have done the trick (and the pipelined functions were much more work) and I suspect performance suffers a little.

  2. Christopher Beck
    December 6, 2008 at 4:35 pm | #2

    Well it’s always best to use the correct technology properly. I can’t stand when I get involved on project that have way over complicated things just to use the latest and greatest technology. K.I.S.S. (Keep It Simple Stoopid) is how I develop applications.

    Like I said in the post, I could have done the non-pipelined function solution, but probably lost out on performance. Plus I like clean, easy to read code and the pipelined function solution is way cleaner IMHO. That is part of the ‘Simple’ I like.

  3. Al Maline
    December 9, 2008 at 3:56 pm | #3

    I run into situations like this frequently in Apex applications whenever I use mutliselect or checkboxes. So I always make sure I have a split and join function available. The following link has a good example:

    http://articles.techrepublic.com.com/5100-10878_11-5259821.html

  4. January 28, 2009 at 10:40 pm | #4

    Just what I was looking for. Great job!

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: