Re: SOLVED: Re: Getting all rows of sqlplus output on one line
- From: rjamya <rjamya@xxxxxxxxx>
- To: "Deepak Sharma" <sharmakdeep_oracle@xxxxxxxxx>
- Date: Tue, 10 Oct 2006 12:58:10 -0400
you have to modify the code for string_agg_type body.
create or replace type body string_agg_type
is
static function ODCIAggregateInitialize(sctx IN OUT string_agg_type)
return number
is
l_delim varchar2(1) default ',';
begin
for x in ( select sys_context('userenv','client_info') client_info
from dual )
loop
if ( x.client_info like 'delim=%' )
then
l_delim := substr( x.client_info, 7, 1 );
end if;
end loop;
sctx := string_agg_type( null, l_delim );
return ODCIConst.Success;
end;
member function ODCIAggregateIterate(self IN OUT string_agg_type,
value IN varchar2 )
return number
is
begin
self.total := self.total || nvl(self.delim,',') || 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,nvl(self.delim,','));
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;
/
then you can specify the delimiter using
dbms_application_info.set_client_info. I think Tom posted this code, I
probably made some very minor changes.
Raj
On 10/10/06, Deepak Sharma <sharmakdeep_oracle@xxxxxxxxx> wrote:
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'
--
//www.freelists.org/webpage/oracle-l
Other related posts:
