Re: How about a *FUNCTION* that'd return a ResultSet ??

From: Eric Givler <egivler_at_flash.net>
Date: Thu, 14 Sep 2000 14:31:07 GMT
Message-ID: <Li5w5.6797$oc3.417662_at_news.flash.net>

create table tab_1 ( chp1 varchar2(2), chp2 varchar2(10) ) /

insert into tab_1 values ('23','chaine1');
insert into tab_1 values ('23','chaine2');
insert into tab_1 values ('23','chaine3');
insert into tab_1 values ('23','chaine4');
insert into tab_1 values ('24','truc_1');
insert into tab_1 values ('24','truc_2');
insert into tab_1 values ('24','truc_3');

create or replace function as_row ( chp_in in tab_1.chp1%type ) return varchar2 is

    retval varchar2(2000);
begin

   for c1_rec in (select chp2

                    from tab_1
                   where chp1 = chp_in
                  order by chp2 )
   loop
       if retval is null then
          retval := c1_rec.chp2;
       else
          retval := retval || ' ' || c1_rec.chp2;
       end if;

   end loop;
   return (retval);
end as_row;
/

rem tab_1_select.sql
column therow format a60 word_wrapped

select chp1

     , as_row(chp1) therow
  from ( select distinct chp1

           from tab_1 )
order by chp1
/

sql>_at_tab_1_select

CH THEROW

-- ------------------------------------------------------------

23 chaine1 chaine2 chaine3 chaine4
24 truc_1 truc_2 truc_3

sql>

<monsri_at_my-deja.com> wrote in message news:8oo08n$47h$1_at_nnrp1.deja.com...
> Hi,
> Let's say I have the following table TAB_1:
>
> CHP_1 CHP_2
> ----- --------------------------------
> 23 chaine1 chaine2 chaine3 chaine4
> 24 truc_1 truc_2 truc_3 truc_4
>
> I'd like to be able to get with this SELECT:
> > SELECT chp1, TORESULTSET(chp2)
> > FROM TAB_1;
>
> the following output:
> CHP_1 TORESULTS
> ----- ---------
> 23 chaine1
> 23 chaine2
> 23 chaine3
> 23 chaine4
> 24 truc_1
> 24 truc_2
> 24 ...
>
> but I'm afraid it's UTTERLY impossible with Oracle
> (I checked the PL/SQL manuals I have at REF CURSOR,
> but all examples are given with procedures; I'd
> really like to perform this with a function...).
>
> If anybody can gimme some hints...
>
> Thanks a lot !
>
> seb
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Thu Sep 14 2000 - 16:31:07 CEST