create or replace function fun1(vstr in varchar2)
return varchar2
is
str varchar2(2000);
cursor gtable is select name2 from tab2 where name1=vstr;
begin
for g in gtable loop
str:=str||g.name2;
end loop;
return(str);
end fun1;
SQL> select * from tab2;
NAME1 NAME2
---------- --------------------------------------------------------------------------------
x aa
x bb
y cc
y dd
SQL>
SQL> select name1,fun1(name1) from tab2 t
2 group by name1
3 ;
NAME1 FUN1(NAME1)
---------- ------------------------------------
x aabb
y ccdd