SQL & PL/SQL

HI all,

I'm having an issue. I want to aggregate some strings but I only need the first 4000 characters of the aggregation.

I just want to check if there is any way to restrict the length of the aggregation prior to it completing other than writing a custom function to do it..

on: Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit

create table foobar (testfield varchar2(1001));

insert into foobar (testfield) values (rpad('x',1001,'x'));
insert into foobar (testfield) values (rpad('y',1001,'x'));
insert into foobar (testfield) values (rpad('z',1001,'x'));
insert into foobar (testfield) values (rpad('w',1001,'x'));

commit;

select substr(max(sys_connect_by_path(testfield,'|')) keep (dense_rank last order by testfield),2,4000)
  from (select testfield,
               row_number() over(order by testfield) cur,
               row_number() over(order by testfield) -1 prev
          from foobar
         --where substr(testfield,1,1) in ('y','z','w')
      )
connect by prev = prior cur
start with cur = 1

ORA-01489: result of string concatenation is too long

thoughts?