Q:

use pipelined function to get result set from multi-tables join

i want to create a function to get selectivity of a table, the input parameter is owner and table name, i searched and find pipelined function can be used here, i used the following codes but i get error for creating function, 
create or replace type ROW_TYPE as object
(
 column_name varchar2(128),
 num_rows number,
 num_distinct number
);
/

create or replace type TABLE_TYPE as table of ROW_TYPE;
/

create or replace function getSelectivity(owner in varchar2, tab_name in varchar2)
return TABLE_TYPE
PIPELINED
IS
BEGIN
 for myrow in(
             select a.column_name,b.num_rows,a.num_distinct
             from dba_tab_col_statistics a, dba_tables b
             where a.owner = b.owner and a.table_name = b.table_name and a.owner=upper(owner) and 
a.table_name =upper(tab_name)
 )loop

pipe row(ROW_TYPE(myrow.column_name,myrow.num_rows,myrow.num_distinct));
end loop;
return;
end;
/

error：
SQL> show errors;
Errors for FUNCTION MOVIL.GETSELECTIVITY:
LINE/COL ERROR
-------- -------------------------------
9/88     PL/SQL: ORA-00918: undefined column
7/14     PL/SQL: SQL Statement ignored
12/20    PLS-00364: Invalid usage of Loop index variable 'MYROW'
12/2     PL/SQL: Statement ignored

but if i just query one single table, then everything is fine, 
create or replace type ROW_TYPE as object
(
 column_name varchar2(128),
 num_distinct number
);
/

create or replace type TABLE_TYPE as table of ROW_TYPE;
/

create or replace function getSelectivity(owner in varchar2, tab_name in varchar2)
return TABLE_TYPE
PIPELINED
IS
BEGIN
 for myrow in(
             select a.column_name,a.num_distinct
             from dba_tab_col_statistics a
             where a.owner=upper(owner) and a.table_name =upper(tab_name)
 )loop

 pipe row(ROW_TYPE(myrow.column_name,myrow.num_distinct));
 end loop;
 return;
 end;
/

does pipelined function do not support multi-tables joining ? 

A:

You can create a view on the tables like the below and use it in your function
       create  or replace view test_db_view as  select 
         a.column_name,b.num_rows,a.num_distinct,
         a.table_name,a.owner
         from all_tab_col_statistics a, all_tables b
         where a.owner = b.owner and a.table_name = b.table_name

   create or replace function getSelectivity(owner in varchar2, tab_name in varchar2)
    return TABLE_TYPE
    PIPELINED
    IS
    BEGIN
     for myrow in( select * from test_db_view a where a.owner=upper(owner) and 
    a.table_name =upper(tab_name)

     )loop

    pipe row(ROW_TYPE(myrow.column_name,myrow.num_rows,myrow.num_distinct));
    end loop;
    return;
    end;

OR you can try using p_owner instead of owner in function getSelectivity parameter

