Pipelining Between PL/SQL Table Functions

Question

I have a package with 2 pipelined functions. When I'm trying to call one function with another function as it's argument I'm getting "ORA-06553: PLS-306: wrong number or types of arguments in call" error.

Here is the package:

create or replace NONEDITIONABLE TYPE RESULTING_RECORD_RT as object
   (
          CALENDAR  NVARCHAR2(1024), 
          PRODUCT   NVARCHAR2(1024), 
          MEASURE   NVARCHAR2(1024), 
          VALUE     NUMBER
   );
/
create or replace NONEDITIONABLE TYPE RESULTING_COLS_RT IS TABLE OF RESULTING_RECORD_RT;
/
create or replace package pipe_pkg as
function pipe_func_emp return RESULTING_COLS_RT PIPELINED;
function pipe_func_emp2(input_Set IN resulting_cols_rt) return RESULTING_COLS_RT PIPELINED;
end;
/
create or replace package body pipe_pkg as
function pipe_func_emp return RESULTING_COLS_RT
PIPELINED
is
    test_tbl resulting_cols_rt:= resulting_cols_rt();
begin
    test_tbl.extend;
    test_tbl(1):=resulting_record_rt('A','B','C',1);
    test_tbl.extend;
    test_tbl(2):=resulting_record_rt('A','B','D',2);
    PIPE ROW(test_tbl(1));
    PIPE ROW(test_tbl(2));
    return;
end;
function pipe_func_emp2(input_Set IN resulting_cols_rt) return RESULTING_COLS_RT
PIPELINED
is
    v_tmp NVARCHAR2(10240);
    l_res SYS_REFCURSOR;
    recs resulting_record_rt;
begin
    open l_res for select * from table(input_Set);
    loop
        fetch l_res into recs;
        PIPE ROW(recs);
        exit when l_res%notfound;
    end loop;
    close l_res;
    return;
end;
end;
/

I'm calling the functions as follows:

select * from TABLE(pipe_pkg.pipe_func_emp2(CURSOR(select * from TABLE(pipe_pkg.pipe_func_emp()))));

And the call throws error:

ORA-06553: PLS-306: wrong number or types of arguments in call to 'PIPE_FUNC_EMP2'
06553. 00000 -  "PLS-%s: %s"

What am I doing wrong?

Answer 1

The function pipe_func_emp2 expected RESULTING_COLS_RT as it's argument, but got REF CURSOR. These are incompatible types.

Try following reproducible example of a chaining of the pipelined functions:

create or replace type somerow  as object (id int, val varchar2 (8))
/
create or replace type sometab is table of somerow
/
create or replace package pack as
    function func1 return sometab pipelined;
    function func2 (cur sys_refcursor) return sometab pipelined;
end;
/
create or replace package body pack as
    function func1 return sometab pipelined is
        tab sometab := sometab (somerow (1,'AAA'), somerow (2,'BBB'));
    begin
        for i in 1..tab.count loop 
            pipe row (tab(i)); 
        end loop;
        return;
    end;
    function func2 (cur sys_refcursor) return sometab pipelined is
        sr somerow;
    begin
        loop
            fetch cur into sr;
            exit when cur%notfound;
            pipe row (sr);
        end loop;
        close cur;
        return;
    end;
end;
/

The query and it's outcome:

select * 
from table (pack.func2 (
    cursor (select value (p) from table (pack.func1()) p )))
/

        ID VAL     
---------- --------
         1 AAA     
         2 BBB