在预言机中使用 listagg 时出错"STRING concatenation too long"

Question

我有一个类似temp_a的表，它有：

acc ai_tab   where
A   B        QQQQ
A   B        RRRR
C   D        SSSS
C   D        TTTT

列where存储的字符串太大。所以，我的预期输出是

acc ai_tab where 
A   B      QQQQ RRRR
C   D      SSSS TTTT

我尝试使用来实现这一点

select acc,ai_tab,LISTAGG(WHERE,'') WITHIN GROUP ORDER BY (acc) "where_cond2" from temp_a
group by acc,ai_tab;

我得到的错误是：

ORA-01489 :result of string concatenation is too long.

我搜索了这个类似的问题，它说也使用XMLCLOB，但它不起作用？我们可以使用函数来获得这个吗？或者还有其他方法吗？

Answer 1

这里有一个示例，显示了您可能使用的两个选项；由于listagg失败，请尝试xmlagg。

SQL> SELECT RTRIM (
2            XMLAGG (XMLELEMENT (e, ename || ' ') ORDER BY empno).EXTRACT (
3               '//text()'),
4            ', ')
5            employees_1,
6         --
7         LISTAGG (ename, ' ') WITHIN GROUP (ORDER BY empno) employees_2
8    FROM emp
9   WHERE deptno = 10;
EMPLOYEES_1                    EMPLOYEES_2
------------------------------ ------------------------------
CLARK KING MILLER              CLARK KING MILLER
SQL>

Answer 2

同意Littlefoot的解决方案。或者，您可以编写自己的函数。我在下面附上了一个样本。注意：我还没有用你的数据测试过这个，所以不确定它能在多大程度上执行

SQL> create table foo as
select 'A' acc, 'B' ai_tab, 'QQQQ' where_column from dual union all
select 'A','B','RRRR' from dual union all
select 'C','D','SSSS' from dual union all
select 'C','D','TTTT' from dual;
Table created.
SQL> select * from foo;
ACC AI_TAB WHERE_COLUMN
--  --     ----
A   B      QQQQ
A   B      RRRR
C   D      SSSS
C   D      TTTT
SQL> create or replace function str_concat (
p_acc       in  foo.acc%type,
p_ai_tab    in  foo.ai_tab%type
)
return clob
is
l_concat  clob;
begin
for o in ( 
select where_column from foo where acc = p_acc and ai_tab =  p_ai_tab  
) loop
l_concat := l_concat || ' ' || o.where_column;
end loop;
return ltrim(l_concat, ' ');
end;
/
Function created.
SQL> select acc, ai_tab, where_column from (
select acc, ai_tab, str_concat(acc, ai_tab) where_column, row_number() over (partition by acc, ai_tab order by null)
) where rn = 1;
ACC AI_TAB  WHERE_COLUMN
--  --      ----
A   B       QQQQ RRRR
C   D       SSSS TTTT