DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws;
  s=s1024; d=yahoo.com;
  h=Message-ID:Received:Date:From:Subject:To:Cc:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding;
  b=FkR/Hc80ze+sdhKWIdI9T9xtXgf7zM99GqeS4v3yKoUIA8zoljnjqV3uTkbh5HiN70QF5zGgm6Vy5y/ih1CsAS6/phSWQdBBIvIp8ByNGM2bilfXsnJDZHgjJfFVgn0jum8L7RcC+wySjKJt+tFw0mgLE5/OBhUHSwdEguYPDqg=  ;
Message-ID: <20061010191506.38403.qmail@web52806.mail.yahoo.com>
Date: Tue, 10 Oct 2006 12:15:05 -0700 (PDT)
From: Deepak Sharma <sharmakdeep_oracle@yahoo.com>
Subject: RE: SOLVED: Re: Getting all rows of sqlplus output on one line
To: Marco.Gralike@AMIS.nl, rjamya@gmail.com, kennaim@gmail.com
Cc: oracle-l@freelists.org
In-Reply-To: <3FA1E6332C73EB4391F729E4EDB7CED96D94BF@amisnt30.AMIS.local>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Sender: oracle-l-bounce@freelists.org
Errors-to: oracle-l-bounce@freelists.org
Precedence: normal
Reply-to: sharmakdeep_oracle@yahoo.com

I used your statement literally and tried it out
(didn't realize it need a change in the function too)

quote> I used a replace around the stragg function but
was wondering if it would be possible (ex:
stragg(col,';'))

BTW, I use another method using 10g's COLLECT feature:

CREATE OR REPLACE
TYPE t_varchar2_tab AS TABLE OF VARCHAR2(4000);
/

CREATE OR REPLACE FUNCTION tab_to_string
(p_varchar2_tab  IN  t_varchar2_tab,  
 p_delimiter     IN  VARCHAR2 DEFAULT ',') 
RETURN VARCHAR2 IS
  l_string     VARCHAR2(32767);
BEGIN
  FOR i IN p_varchar2_tab.FIRST .. p_varchar2_tab.LAST
LOOP
    IF i != p_varchar2_tab.FIRST THEN
      l_string := l_string || p_delimiter;
    END IF;
    l_string := l_string || p_varchar2_tab(i);
  END LOOP;
  RETURN l_string;
END tab_to_string;
/

select tab_to_string( 
 CAST(COLLECT( username ) AS t_varchar2_tab),';' ) 
    AS username
from dba_users
where username like 'SYS%';

USERNAME
========
SYSTEM;SYS;SYSMAN


--- Marco Gralike <Marco.Gralike@AMIS.nl> wrote:

> I have NO idea what you wrote about, but google gave
> me the answer within two clicks and typing one word
> "stragg". Look simple to me...
>  
>
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:2196162600402
> 
> ________________________________
> 
> From: oracle-l-bounce@freelists.org on behalf of
> Deepak Sharma
> Sent: Tue 10-10-2006 18:52
> To: rjamya@gmail.com; kennaim@gmail.com
> Cc: oracle-l@freelists.org
> Subject: Re: SOLVED: Re: Getting all rows of sqlplus
> output on one line
> 
> 
> 
> How?
> 
> select stragg(username,';') users
> from dba_users
> where username like 'SYS%'
> order by username
> /
> select stragg(username,';') users
>        *
> ERROR at line 1:
> ORA-06553: PLS-306: wrong number or types of
> arguments
> in call to 'STRAGG'
> 
> 
> --- rjamya <rjamya@gmail.com> wrote:
> 
> > yes, try it, it is very simple actually. I had
> done
> > it some time ago.
> >
> > Raj
> >
> > On 10/9/06, Ken Naim <kennaim@gmail.com> wrote:
> > > Is there a way to modify the stragg and
> > ODCIAggregateIterate function to
> > > accept a variable for the separator. I used a
> > replace around the stragg
> > > function but was wondering if it would be
> possible
> > (ex: stragg(col,';'))
> 
> 
> __________________________________________________
> Do You Yahoo!?
> Tired of spam?  Yahoo! Mail has the best spam
> protection around
> http://mail.yahoo.com <http://mail.yahoo.com/> 
> --
> http://www.freelists.org/webpage/oracle-l
> 
> 
> 
> 
> 
> 


__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 
--
http://www.freelists.org/webpage/oracle-l