sql - 连接太长

Question

假设我有这张表:

ID|Col1|Col2|Col3

1|Text that has 4000 chars|Text2 that has 4000 chars|Text3 that has 4000 chars

2|Text4 that has 4000 chars|Text5 that has 4000 chars|Text6 that has 4000 chars

3|Text7 that has 4000 chars|Text8 that has 4000 chars|Text9 that has 4000 chars

我像这样使用listagg:

SELECT id,
       listagg(col1||col2||col3, ',') within group (order by id)
FROM table;

我遇到了错误:

ORA-01489: result of string concatenation is too long

经过研究，我发现使用 xmlagg 可以解决这个问题( link )，但后来意识到真正的问题在于 col1、col2 和 col3 的串联，因为它仅限于 4000 个字符，所以使用 xmlagg 会仍然返回相同的错误。

有人已经解决这个问题了吗？或者没有解决方法吗？ (link)

更新:

为了清楚起见，我更新了表格上的示例值(以便 Kumar 先生理解)，我的预期输出应该类似于:

ID | Agg
1 | Text that has 4000 charsText2 that has 4000 charsText3 that has 4000 chars
2 | Text4 that has 4000 charsText5 that has 4000 charsText6 that has 4000 chars
3 | Text7 that has 4000 charsText8 that has 4000 charsText9 that has 4000 chars

这显然不起作用。

Answer 1

您可以做得更简单，因为 Oracle 推出了 SQL Semantics and LOBs前段时间。

SELECT ID, TO_CLOB(col1) || col2 || col3 AS very_long_text
FROM TABLE;

|| 运算符的第一个元素必须是 CLOB，然后它才能工作。