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
> Boyle, Christopher wrote:
>
>>> Boyle, Christopher wrote:
>>>> With code BOLDLY lifted from AskTom.oracle.com:
>>>>
>>>> create or replace type string_agg_type as object
>>>> (
>>>> total varchar2(32000),
>>>>
>>>> static function
>>>> ODCIAggregateInitialize(sctx IN OUT string_agg_type )
>>>> return number,
>>>>
>>>> member function
>>>> ODCIAggregateIterate(self IN OUT string_agg_type ,
>>>> value IN varchar2 )
>>>> return number,
>>>>
>>>> member function
>>>> ODCIAggregateTerminate(self IN string_agg_type,
>>>> returnValue OUT varchar2,
>>>> flags IN number)
>>>> return number,
>>>>
>>>> member function
>>>> ODCIAggregateMerge(self IN OUT string_agg_type,
>>>> ctx2 IN string_agg_type)
>>>> return number
>>>> );
>>>> /
>>>>
>>>>
>>>> create or replace type body string_agg_type
>>>> is
>>>>
>>>> static function ODCIAggregateInitialize(sctx IN OUT
>>> string_agg_type)
>>>> return number
>>>> is
>>>> begin
>>>> sctx := string_agg_type( null );
>>>> return ODCIConst.Success;
>>>> end;
>>>>
>>>> member function ODCIAggregateIterate(self IN OUT string_agg_type,
>>>> value IN varchar2 )
>>>> return number
>>>> is
>>>> begin
>>>> self.total := self.total || ',' || value;
>>>> return ODCIConst.Success;
>>>> end;
>>>>
>>>> member function ODCIAggregateTerminate(self IN string_agg_type,
>>>> returnValue OUT varchar2,
>>>> flags IN number)
>>>> return number
>>>> is
>>>> begin
>>>> returnValue := ltrim(self.total,',');
>>>> return ODCIConst.Success;
>>>> end;
>>>>
>>>> member function ODCIAggregateMerge(self IN OUT string_agg_type,
>>>> ctx2 IN string_agg_type)
>>>> return number
>>>> is
>>>> begin
>>>> self.total := self.total || ctx2.total;
>>>> return ODCIConst.Success;
>>>> end;
>>>>
>>>>
>>>> end;
>>>> /
>>>>
>>>>
>>>>
>>>> CREATE or replace
>>>> FUNCTION stragg(input varchar2 )
>>>> RETURN varchar2
>>>> PARALLEL_ENABLE AGGREGATE USING string_agg_type;
>>>> /
>>>> .
>>>>
>>>>
>>>> select stragg(site_id)
>>>> from site_list_table
>>>> where definition_id in (nnnn, nnn2, nnn3)
>>>> order by site_id;
>>>>
>>>>
>>>> Should do what I think you are asking.
--
http://www.freelists.org/webpage/oracle-l
Received on Thu Oct 05 2006 - 16:35:23 CDT
