Nested PIPELINED function

Question

create type data_type_1 as object (x number, y number)
/

create type table_type_1 as table of data_type_1
/

create or replace package xyz AS
      function main_xyz return table_type_1 pipelined;
      function sub_func return table_type_1 pipelined;
      function sub_func1 return table_type_1 pipelined;
end xyz;
/

create package body XYZ AS
    function main_xyz return data_type_1 pipelined is
        begin 
        --code
        --pipe row(sub_func); --edit_1
        FOR rec in (select * from table(sub_func1(x,y))) LOOP
               pipe row(rec);
        END LOOP;
        end;
    --function sub_func return data_type_1 pipelined is --edit_1
        --begin --edit_1
        --code --edit_1
        --pipe row(def); --def is data_type_1 --edit_1
        --end; --edit_1
     function sub_func_1(x in number, y in number) return data_type_1 pipelined is
        begin 
        --code
        loop
        pipe row(abc); --abc is data_type_1
        end loop;
        end;
 end;
 create package body ABC AS
     function main_ABC is
        begin 
        --code
        FOR rec in (select * from table(main_xyz)) LOOP
               pipe row(rec);
        END LOOP;
        end;
 end;

Error that I obtain is...

Error is showed in the block of main_xyz where sub_func1 is called.

[Error] PLS-00382 (): PLS-00382: expression is of wrong type
[Error] PLS-00306 (): PLS-00306: wrong number or types of arguments in call to
[Error] ORA-00904 (): PL/SQL: ORA-00904: : invalid identifier
[Error] PLS-00364 (): PLS-00364: loop index variable 'REC' use is invalid

What is wrong in the above code? and why?

@diziaq What do you think? – user256378 Jan 20 '16 at 10:05 — user256378, Jan 20 '16 at 10:05

Alex Poole · Accepted Answer · 2016-01-20T13:00:09.980

Your functions are returning data_type_1, and the table collection is trying to consume that too. But both need a collection type, even if you expect them to only return a single value (in which case there isn't much point pipelining). You can't pipe a collection type directly, you pipe a member of the collection. So data_type_1 should be a scalar or object/record type, and you need another type which is a collection of those.

create type data_type_1 as object (x number, y number)
/

create type table_type_1 as table of data_type_1
/

create or replace package xyz AS
  function main_xyz return table_type_1 pipelined;
  function sub_func return table_type_1 pipelined;
  function sub_func1 return table_type_1 pipelined;
end xyz;
/

create or replace package body xyz as
  function main_xyz return table_type_1 pipelined is
  begin 
    --code
    for rec in (select * from table(sub_func)) loop
      pipe row(data_type_1(rec.x, rec.y));
    end loop;
    for rec in (select * from table(sub_func1)) loop
      pipe row(data_type_1(rec.x, rec.y));
    end loop;
  end;

  function sub_func return table_type_1 pipelined is
    def data_type_1;
  begin 
    --code
    pipe row(def); --def is data_type_1
  end sub_func;

  function sub_func1 return table_type_1 pipelined is
    abc data_type_1;
  begin 
    --code
    loop
      pipe row (abc); --abc is data_type_1
    end loop;
  end sub_func1;
end xyz;
/

So I've added a table type of your existing data_type_1, and changed the function definitions to return that table type instead. The pipe row still uses data_type_1 - each is a row in the table type. Your loop needs a query for its cursor, not a direct call to table(), so I've changed that too. And the pipe row(sub_func); also needs to be a similar loop over a query.

You only tagged this as PL/SQL but because you may intend to call main_xyz from plain SQL, and because you're calling the sub-functions from a SQL context in those loops, data_type_1 and table_type_1 need to be created at schema level rather than in PL/SQL. (This has changed a bit in 12c but not enough to help here).

If you wanted to have them as PL/SQL types, declared in the package specification, then you couldn't call the function from a non-PL/SQL context, and you'd have to replace the loops with a call to the function followed by an iteration over the returned collection.

Yes I have implemented the way you have descibed, inspite of which i am getting the error.. — user256378, Jan 20 '16 at 12:27
@user256378 - I'd missed some parentheses in the loop syntax; they have to be schema-level types to use them in those cursor loops; and I hadn't referred to `rec` fields properly. Updated with something that compiles, at least. — Alex Poole, Jan 20 '16 at 13:01
@user256378 - your edits might be a bit extreme; they're hiding some of the original problem now. But I think I've already addressed the current issue, at least part of it - you can't pipe `rec` directly back out; and what you've shown is still returning `data_type_1` instead of the table type. The version in my answer [compiles without errors](http://sqlfiddle.com/#!4/66858/1). — Alex Poole, Jan 20 '16 at 13:52
@user256378 - which part isn't answered? The version in the updated question still doesn't use the table type as the return type, and doesn't refer to `rec` components properly. The SQL Fiddle I linked to shows it working. You've made it quite abstract so can't really help with the ORA-00904; unless your call to `main_xyz` really isn't prefixed with the first package name, e.g. `xyz.main_xyz`. — Alex Poole, Jan 21 '16 at 16:42

Nested PIPELINED function

1 Answers1

Linked