oracle - Stored PL/SQL with select clause -

tables :

//team name -------- fly box fire 

second table:

//player name     pname -------------- fly      abc box      gba fly      abcd fly      john 

so try created pl/sql following code:

create or replace function test( tname in varchar2 )     total number := 0;     p_np player.pname%type;      cursor c_player          select listagg(p.pname,',') within group(order p.pname)          player p pname = tname;  begin     select count(*) total player name =tname;     open c_player;     fetch c_player p_no;     exit when c_player%notfound;      dbms_output.put_line( total || ':' || p_np );     close c_player; end test; / 

but when run script pass input in sqlplus:

execute test('fly'); 

but question use select statement show out. how should it?

expected output:

3 : abc , abcd , john 

but need use select statement

select test('fly');