Pipelined function calling another pipelined function.

Question

Here's a package with two pipelined functions:

create or replace type tq84_line as table of varchar2(25);
/

create or replace package tq84_pipelined as

    function more_rows return tq84_line pipelined;
    function go        return tq84_line pipelined;

end tq84_pipelined;
/

Ant the corresponding package body:

create or replace package body tq84_pipelined as

    function more_rows return tq84_line pipelined is
    begin

        pipe row('ist');
        pipe row('Eugen,');

        return;

    end more_rows;

    function go return tq84_line pipelined is
    begin

        pipe row('Mein');
        pipe row('Name');

        /* start */
        for next in (
          select column_value line from table(more_rows)
        ) 
        loop
          pipe row(next.line);
        end loop;
        /* end */

        pipe row('ich');
        pipe row('weiss');
        pipe row('von');
        pipe row('nichts.');

    end go;

end tq84_pipelined;
/

The important thing is that go sort of calls more_rows with the for next in ... between /* start */ and /* end */

I can use the package as follows:

select * from table(tq84_pipelined.go);

This is all fine and dandy, but I hoped I could replace the lines between /* start */ and /* end */ with a simple call of more_rows.

However, this is obviously not possible, as it generetes a PLS-00221: 'MORE_ROWS' is not a procedure or is undefined.

So, my question: is there really no way to shortcut the loop?

EDIT

Obviously, from the answers so far, my question was not clear.

The package, as stated works.

But I am bothered with the 6 (that is: SIX) lines between the markers /* start */ and /* end */. I'd like to replace these with one single line. But I havent found any way doing that.

Answer 1

Try select column_value line from table(tq84_line.more_rows) i.e. include the package name in the query.

Answer 2

The point of pipelined functions is to feed TABLE() functions. I don't think there is any way to avoid it. Unfortunately we have to assign its output to a PL/SQL variable. We can't assign a pipelined function to a nested table like this nt := more_rows; due to

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

So SELECT ... FROM TABLE() it has to be.

I have a slightly different solution for your consideration. I don't know whether it solves your underlying problem.

create or replace package body tq84_pipelined as 

    function more_rows return tq84_line pipelined is 
    begin 

        pipe row('ist'); 
        pipe row('Eugen,'); 

        return; 

    end more_rows; 

    function go return tq84_line pipelined is 
        nt1 tq84_line;
        nt2 tq84_line;
        nt3 tq84_line;
        nt0 tq84_line;
    begin 

        nt1 := tq84_line('Mein','Name'); 

        select * 
        bulk collect into nt2
        from table(more_rows);

        nt3 := tq84_line('ich','weiss','von','nichts.'); 

        nt0 := nt1 multiset union nt2 multiset union nt3; 

        for i in nt0.first..nt0.last
        loop 
          pipe row(nt0(i)); 
        end loop; 

        return;

    end go; 

end tq84_pipelined; 
/

As I'm sure you're aware (but for the benefit of other seekers) the MULTISET UNION syntax for glomming collections together was introduced in Oracle 10g.

This version of GO() produces the same output as your original implementation:

SQL> select * from table( tq84_pipelined.go)
  2  /

COLUMN_VALUE
-------------------------
Mein
Name
ist
Eugen,
ich
weiss
von
nichts.

8 rows selected.

SQL>