3

I have to write a nested pipelined function in pl/sql which I tried implementing in the following manner. 

create package body XYZ AS
    function main_xyz return data_type_1 pipelined is
        begin 
        --code
        pipe row(sub_func);
        end;
    function sub_func return data_type_1 pipelined is
        begin 
        --code
        pipe row(sub_func_1);
        end;
     function sub_func_1 return data_type_1 pipelined is
        begin 
        --code
        pipe row(main_abc);
        end;
 end;

create package body abc AS
        function main_abc return data_type_2 pipelined is
            var data_type_2;
            begin 
            --code
             return var;
            end;
  end;

However, I get the following error

[Error] PLS-00653 : PLS-00653: aggregate/table functions are not allowed in PL/SQL scope

Where am I going wrong? Is it syntax or logic?

user256378
  • 35
  • 9

1 Answers1

2

Pipelined functions provide rows one by one (on demand), so you cannot put all the rows at one time from pipelined function.

Seems to me you need to change main_xyz this way:

function main_xyz return data_type_1 pipelined is
 begin 

   --code

   FOR rec IN (select * from table(XYZ.sub_func)) LOOP
       pipe row(rec);
   END LOOP;
 end;

Consider that sub_func must be in specification of XYZ package since everything you use in SQL queries including PIPELINED functions are to be public (i.e. visible to a user who runs query).

UPDATE: I forget to alert: do not abuse pipelined functions (if you have another choice) - queries using them might have lame performance because DB engine cannot build a good execution plan for "unpredictable piped rows".

diziaq
  • 6,881
  • 16
  • 54
  • 96