Pipe nested object type

Question

I am just trying to achieve pipe row nested type. There are tons of examples around but none that I am able to apply.

My types are:

create type t1_row as object ( a1 number, a2 varchar2(10) );
create type t1_tab as table of t1_row; 
create type t2_row as object ( b1 number, b2 varchar2(10), b3 t1_tab );
create type t2_tab as table of t2_row;

I've tried to create a function in so many ways, but none of them are able to compile successfully.

One example:

create or replace function fn (r in number) return t2_row pipelined is
    l_row1 t1_tab;
    l_row2 t2_tab;
begin
    for i in 1..r loop            
        for j in 1..r loop
            l_row1(j).a1 := j;
            l_row1(j).a2 := 'a2 ' || j;
        end loop;
        l_row2(i) := (i,l_row1);
        PIPE ROW (l_row2);
    end loop;
    return;
end;

This code produces the following errors:

[Error] PLS-00630 (1: 12): PLS-00630: pipelined functions must have a supported collection return type

[Error] PLS-00382 (10: 22): PLS-00382: expression is of wrong type

Any help advice or any similar example can be useful.

Version: Oracle 11g Release 11.2.0.1.0

Answer 1

Your code has two syntax errors and a logic error.

The first syntax error is that the function's return type should be a collection not a row type, so

 return t2_tab pipelined

The second syntax error is that you need to include the type when instantiating an object, and the number of arguments must match the signature of the type. So the outer loop assignment should be:

    l_row2(i) := t2_row(i, 'T2', l_row1);

The logic error is that we don't need to maintain a collection variable for the output. We just need a row variable.

Also the indexed counts seem a bit confused, so my code may differ from your intention.

create or replace function fn (r in number) 
    return t2_tab pipelined 
is
    l_tab1 t1_tab;
    l_row2 t2_row;
begin
    for i in 1..r loop            
        l_tab1 := new t1_tab();
        l_tab1.extend(r);
        for j in 1..r loop
            l_tab1(j) := t1_row(j*i, 'a2 ' || j);
        end loop;
        l_row2 := t2_row(i, 'T2', l_tab1);
        PIPE ROW (l_row2);
    end loop;
    return;
end;
/

Here is the run:

SQL> select * from table(fn(3));

   B1 B2  B3(A1, A2)
----- --- ---------------------------------------------------------------
    1 T2  T1_TAB(T1_ROW(1, 'a2 1'), T1_ROW(2, 'a2 2'), T1_ROW(3, 'a2 3'))
    2 T2  T1_TAB(T1_ROW(2, 'a2 1'), T1_ROW(4, 'a2 2'), T1_ROW(6, 'a2 3'))
    3 T2  T1_TAB(T1_ROW(3, 'a2 1'), T1_ROW(6, 'a2 2'), T1_ROW(9, 'a2 3'))

SQL>