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

From: rjamya <rjamya_at_gmail.com>
Date: Tue, 10 Oct 2006 12:58:10 -0400
Message-ID: <9177895d0610100958y50c63461q879265ab44aa5129@mail.gmail.com>

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_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-l

Received on Tue Oct 10 2006 - 11:58:10 CDT