Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> Re: SOLVED: Re: Getting all rows of sqlplus output on one line
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;
member function ODCIAggregateIterate(self IN OUT string_agg_type,
value IN varchar2 )return number
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
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
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_at_yahoo.com> 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'
>
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Oct 10 2006 - 11:58:10 CDT
![]() |
![]() |