Help with Oracle Pipelined function

oracleoracle-10gpivot

I have a pivot where I have a contract with tank numbers separated by ; and I've been given this example to pivot them down into rows (which works):

SELECT oks_c.contract_number, 
       oks_l.line_id,
       oks_l.tank_numbers, column_value AS TANK_NUMBER 
  FROM oks_contract_header oks_c
 INNER JOIN oks_contract_lines oks_l 
    ON oks_l.contract_number = oks_c.contract_number AND 
       oks_l.item_name LIKE '%.55201'
    , table(str2tbl(oks_l.tank_numbers,';')) acbb
ORDER BY oks_c.contract_number, 
         oks_l.line_id, 
         TANK_NUMBER

Here's the pipelined function:

CREATE OR REPLACE function DBRAJAH.str2tbl( p_str in varchar2, p_delim in varchar2 default ',' ) return 
str2tblType
PIPELINED
as
    l_str      long default p_str || p_delim;
    l_n        number;
begin
    loop
        l_n := instr( l_str, p_delim );
        exit when (nvl(l_n,0) = 0);
        pipe row( ltrim(rtrim(substr(l_str,1,l_n-1))) );
        l_str := substr( l_str, l_n+1 );
    end loop;
    return;
end;

But I'm trying to understand why it works. Particularly how the pipelined function results are not cross-joined?

I'm familiar with outer apply and table-valued functions in SQL Server, but this seems to be quite different – or is this really an OUTER APPLY?

Best Answer

It's not cross-joined because the pipelined function is row-dependent; you can't cross-join row 1's values from the pipelined function to row 2's values because row 1's pipelined function values don't exist anymore.

If the pipelined function weren't row-dependent - if it went off of a static value - I suspect it would cross-join. (Try it and see!)

Related Question