SQL & PL/SQL

Consider following example:

create table TableTest(
Numer varchar2(100)
);
/

create or replace TYPE X_TEST AS OBJECT(RPARAMVALUE VARCHAR2(100));
/
create or replace TYPE Y_TEST AS TABLE OF X_TEST;
/
create or replace FUNCTION SP_TEST (IParam varchar2 )
RETURN Y_TEST PIPELINED
AS
begin
RETURN;
end;
/

insert into TableTest values('oracle');
insert into TableTest values('database');
insert into TableTest values('10g');


Table TableTest has 3 records:
select T1.* from TableTest T1;

oracle
database
10g

3 rows selected

When I modify the statement - add left outer join with Table pipelined function I should receive at least 3 record:
select T1.* from TableTest T1
left outer join Table(sp_test(T1.Numer)) T2 on 1=1;

_0 rows selected_

Could you please tell me how it's possibile that LEFT OUTER JOIN in such case returns no records ??

When I modify the statement, it returns 3 records;
select T1.* from TableTest T1
left outer join Table(sp_test('test')) T2 on 1=1;

oracle
database
10g

3 rows selected


When I modify pipelined function - it always returns one record, everything works fine:
create or replace FUNCTION SP_TEST (IParam varchar2 )
RETURN Y_TEST PIPELINED
AS
begin
PIPE ROW(X_TEST(NULL));
RETURN;
end;
/

select T1.* from TableTest T1
left outer join Table(sp_test(T1.Numer)) T2 on 1=1;


oracle
database
10g

3 rows selected

Edited by: user3403624 on 2009-02-13 02:54