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.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, 

Here's the pipelined function:

CREATE OR REPLACE function DBRAJAH.str2tbl( p_str in varchar2, p_delim in varchar2 default ',' ) return 
    l_str      long default p_str || p_delim;
    l_n        number;
        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;

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?

  • 1
    My guess would be that your tank_numbers only contains a single "element". But why do you mix implicit joins and explicit joins? That's very bad coding style.
    – user1822
    Aug 20, 2013 at 19:13
  • @a_horse_with_no_name No, the TANK_NUMBERS column is delimited with ; and have multiple items in the string and the results are correct. Someone gave me this code because using a CONNECT BY with a regexp to do the pivoting was not working in Oracle 10g (query was not completing, and would eventually complete with some trivial filtering, but it was unworkable). I don't believe you can use a regular join to a table that is different for each row? Or can you? I have a huge blind spot in Oracle table-valued functions, and how does COLUMN_VALUE work here?
    – Cade Roux
    Aug 20, 2013 at 20:49

1 Answer 1


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!)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.