RE: SOLVED: Re: Getting all rows of sqlplus output on one line
- From: "Ken Naim" <kennaim@xxxxxxxxx>
- To: <sharmakdeep_oracle@xxxxxxxxx>, <Rodd.Holman@xxxxxxxxx>, "'Boyle, Christopher'" <Christopher.Boyle@xxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 9 Oct 2006 16:04:30 -0500
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Deepak Sharma
Sent: Thursday, October 05, 2006 5:09 PM
To: Rodd.Holman@xxxxxxxxx; Boyle, Christopher; oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxx> 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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
