Hi,
on my 9.2.0.8 I did test like that
create table t1 as select rownum r from dba_source s1, dba_source s2 where rownum <= 100000 ;
--then decided to simplify :)
update t1 set r = 1 ;
commit;
with data as
(
select r, row_number() over (order by r) rn, count(*) over () cnt
from
(
select r from t1 where rownum <=2000
)
)
select sys_connect_by_path(r, ',') agg
from data
where
rn = cnt
start with rn = 1
connect by prior rn = rn-1
/
--thats ok but
with data as
(
select r, row_number() over (order by r) rn, count(*) over () cnt
from
(
select r from t1 where rownum <=2001
)
)
select sys_connect_by_path(r, ',') agg
from data
where
rn = cnt
start with rn = 1
connect by prior rn = rn-1
/
ORA-01489: result of string concatenation is too long
So , I'm probably hitting varchar2 4k limitation , right ?
Question how to workaround this ?
Basically I want to generate one long line using only sql , let us say I need limit like 32k characters .
All should work with 9.2 .
Regards
GregG