How to include single row multiple column subquery (PIPELINED function) results in the result set

Question

I am using Oracle 11g.

So, lets say that I have a test data table like this one

with test_data as (
    select 1 as id, 'John' as name from dual 
        union all
    select 2 as id, 'Jack' as name from dual
        union all
    select 3 as id, 'Peter' as name from dual
)

Also I have a piplined function that returns one row per call, with multiple columns like the following:

CREATE OR REPLACE PACKAGE MY_PACK AS
  TYPE result_record is RECORD(
   surname           varchar2(27),
   telephone          varchar2(50),
   place_of_birth     varchar2(50)
       );

  TYPE result_table IS TABLE OF result_record;
  function runPipe(id number) RETURN result_table PIPELINED;
END ERC_PACK;
/

CREATE OR REPLACE PACKAGE BODY MY_PACK AS
    function runPipe(id number) RETURN result_table PIPELINED IS
    rec           result_record:=null;
    begin
       if  (id=1) then
         select 'Smih','139289289','Paris' into rec from dual;
       end if;
       if  (id=2) then
         select 'Lock','1888888888','London' into rec from dual;
       end if;
       if  (id=3) then
         select 'May','99999999999','Berlin' into rec from dual;
       end if;
       pipe row (rec);
       return;
  end;
END ERC_PACK;
/

And of course the

select * from table(MY_PACK.runPipe(1))

returns

Smih    139289289   Paris

Now I would like a select statement that will return all the rows of the test_data along with the corresponding values from pipeline function

e.g. something like

select test_data.*, (select * from table(MY_PACK.runPipe(id))) from test_data

which of course doses not work but the expected results would be something like:

1 John  Smih    139289289   Paris
2 Jack  Lock    1888888888  London
3 Peter May     99999999999 Berlin

So how to achieve the above expected result, given the test_data table and the pipelined function?

score 2 · Accepted Answer · answered Nov 15 '17 at 08:59

2

try this select :

    with test_data as (
    select 1 as id, 'John' as name from dual 
        union all
    select 2 as id, 'Jack' as name from dual
        union all
    select 3 as id, 'Peter' as name from dual
)
select td.*, s.*
from test_data td
LEFT JOIN  table(MY_PACK.runPipe(td.id)) s ON 1 = 1;

answered Nov 15 '17 at 08:59

Mehdi Ghasri

488
3
10

This works well. But you can put inner join rather than left join. – XING Nov 15 '17 at 09:05
This actually worked - even with `from test_data td, table(MY_PACK.runPipe(td.id)) s` (no need for left join) ... too bad one cannot do the same with `from test_data td, (select * table(MY_PACK.runPipe(td.id))) s ` it would be really helpful in my real world problem - however that wasn't in my initial question - so I am accepting. Thanks!!! – PKey Nov 16 '17 at 13:01

score 0 · Answer 2 · answered Nov 15 '17 at 08:21

0

You could use OUTER APPLY:

select td.*, s.*
from test_data td
outer apply (select * from table(MY_PACK.runPipe(td.id))) s

answered Nov 15 '17 at 08:21

Lukasz Szozda

162,964
23
234
275

Is this for 12c? Any other way for 11g? – PKey Nov 15 '17 at 08:28
@Plirkee Oracle 12c – Lukasz Szozda Nov 15 '17 at 08:28
Unfortunately I have 11g ... any ideas? – PKey Nov 15 '17 at 08:31

score 0 · Answer 3 · answered Nov 15 '17 at 14:51

Kindly Try the below with alias

with test_data as (
select 1 as id, 'John' as name from dual 
    union all
select 2 as id, 'Jack' as name from dual
    union all
select 3 as id, 'Peter' as name from dual
)
select * from table(MY_PACK.runPipe(test_data.id))) from test_data

How to include single row multiple column subquery (PIPELINED function) results in the result set

3 Answers3