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?
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;