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?
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;
/