standalone pipelined function calling another standalone pipelined function

Question

I want to write two pipelined functions, standalone, meaning outside of PL/SQL package:

create or replace function fn_test_1 
return sys.DBMS_DEBUG_VC2COLL pipelined -- ODCIVARCHAR2LIST 
AS
BEGIN
  FOR l_row in ( ... )
  LOOP
    PIPE ROW('text');
    PIPE ROW('other text');
    PIPE ROW(strings_concatenated);
  END LOOP;
END;
/

create or replace function fn_test_2 
return sys.DBMS_DEBUG_VC2COLL pipelined -- ODCIVARCHAR2LIST 
AS
BEGIN
  FOR l_row in ( select column_value as line from TABLE( fn_test_1 ) )
  LOOP
    PIPE ROW(l_row);
  END LOOP;
END;
/

fn_test_1 compiles successfully and works fine. However I cannot compile fn_test_2 becuase of:

PLS-00382: expression is of wrong type

Can I even write standalone pipelined functions one calling the other?

Answer 1

You're return a cursor and not the value it has, use this:

create or replace function fn_test_2 
return sys.DBMS_DEBUG_VC2COLL pipelined -- ODCIVARCHAR2LIST 
AS
BEGIN
  FOR l_row in ( select column_value as line from TABLE( fn_test_1 ) )
  LOOP
    PIPE ROW(l_row.line);
  END LOOP;
END;
/