Hi,
I tried this query to use on my database (took it from [http://tkyte.blogspot.com/2007_03_01_archive.html|http://tkyte.blogspot.com/2007_03_01_archive.html] )
SQL> select deptno,
2 max(sys_connect_by_path(ename, ' ' )) scbp
3 from (select deptno, ename, row_number() over (partition by deptno order by ename) rn
4 from emp
5 )
6 start with rn = 1
7 connect by prior rn = rn-1 and prior deptno = deptno
8 group by deptno
9 order by deptno
10 /
DEPTNO SCBP---------- ----------------------------------------
10 CLARK KING MILLER
20 ADAMS FORD JONES SCOTT SMITH
30 ALLEN BLAKE JAMES MARTIN TURNER WARD
When I use the same query on my table in my database (not this one) I have a lot of results and I get this error:
ORA-01489: result of string concatenation is too long
(I can have between 1 et 500 entries, different lenghts)
I made some search in the forum and Google and I think I can use the function TO_CLOB but it doesen't work. (Line 3) select deptno, (to_clob(ename)),
SQL> select deptno,
2 max(sys_connect_by_path(ename, ' ' )) scbp
3 from (select deptno, (to_clob(ename)), row_number() over (partition by deptno order by ename) rn
4 from emp
5 )
6 start with rn = 1
7 connect by prior rn = rn-1 and prior deptno = deptno
8 group by deptno
9 order by deptno
10 /
ORA-00904: string: invalid identifier
My Question: can I use to_clob in this query?
thanks, Roseline
Edited by: Roseline on 2009-09-25 12:06