RE: SOLVED: Re: Getting all rows of sqlplus output on one line

From: Ken Naim <kennaim_at_gmail.com>
Date: Mon, 9 Oct 2006 16:04:30 -0500
Message-ID: <019001c6ebe6$84472ba0$85e7cf48@KenHome>

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,';'))

Thanks,
Ken

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Deepak Sharma
Sent: Thursday, October 05, 2006 5:09 PM To: Rodd.Holman_at_gmail.com; Boyle, Christopher; oracle-l_at_freelists.org Subject: Re: SOLVED: Re: Getting all rows of sqlplus output on one line

Take care when you want the columns to be in order when using stragg():

select stragg(username) users
from dba_users
where username like 'SYS%'
order by username
/
USERS

SYSTEM,SYS,SYSMAN Correct-->

select stragg(username) users
from (

        select username
        from dba_users
        where username like 'SYS%'
        order by username

)

USERS

SYS,SYSMAN,SYSTEM

Rodd Holman <Rodd.Holman_at_gmail.com> wrote:

> Thanks to Christopher Boyle and AskTom for the Type
> and function.
> Here's what I ended up with after creating them.
>
> set heading off
> set feedback off
> set linesize 32000
> set pagesize 0
> Spool c:\temp\sitelist.txt
> select stragg(site_id) TEXT_OUTPUT
> from lgrsdss_data_set_sites
> where definition_id in ('nnn1','nnn2','nnn3')
> order by site_id
> /
> Spool off
> edit c:\temp\sitelist.txt
>
> This opens notepad on the user's desktop and let's
> him select the whole thing as one string for
> pasting.
>
> Thanks to all who replied.
>
> Rodd

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


--
http://www.freelists.org/webpage/oracle-l

Received on Mon Oct 09 2006 - 16:04:30 CDT