CREATE TABLE t_row_str(
ID INT,
col VARCHAR2(10));
INSERT INTO t_row_str VALUES(1,'a');
INSERT INTO t_row_str VALUES(1,'b');
INSERT INTO t_row_str VALUES(1,'c');
INSERT INTO t_row_str VALUES(2,'a');
INSERT INTO t_row_str VALUES(2,'d');
INSERT INTO t_row_str VALUES(2,'e');
INSERT INTO t_row_str VALUES(3,'c');
COMMIT;
SELECT * FROM t_row_str;
SELECT t.id id, substr(sys_connect_by_path(t.col, ','), 2) str
FROM (SELECT id, col, row_number() over(PARTITION BY id ORDER BY col) rn
FROM t_row_str) t
WHERE connect_by_isleaf = 1
START WITH rn = 1
CONNECT BY rn = PRIOR rn + 1
AND id = PRIOR id;
当我执行这个语句会报错，ORA-01489: 字符串连接的结果过长
因为substr(sys_connect_by_path(t.col, ','), 2) str字段 我的查询结果有上万个汉字，所以无法处理。
考虑将col转换成clob，小数据量没有问题，当将所有数据都放进去的时候就报错了！ORA-01489: 字符串连接的结果过长
问下有没有解决方法！谢谢！
|
