Oracle字符串连接太长

Question

我正在尝试执行下面的查询，该查询通过将值传递给变量中的pivot来透视表。

DECLARE
cols VARCHAR2(30000);
BEGIN
SELECT
LISTAGG(''''
|| agr_name
|| ''' as "'
|| agr_name
|| '"', ',') WITHIN GROUP(
ORDER BY
agr_name
)
INTO cols
FROM
(
SELECT DISTINCT
to_char(agr_name) AS agr_name
FROM
dat_skills
--            WHERE
--                ROWNUM < 400
);
EXECUTE IMMEDIATE q'[
CREATE OR REPLACE VIEW vw_dat_skills AS
SELECT
*
FROM
dat_skills PIVOT (
COUNT ( agr_name )
FOR agr_name
IN (]'||cols||q'[)
)
]'
;
END;

但是我得到下面的错误:

ORA-01489: result of string concatenation is too long
01489. 00000 -  "result of string concatenation is too long"
*Cause:    String concatenation result is more than the maximum size.
*Action:   Make sure that the result is less than the maximum size.

如果我执行select distinct，它将检索138行，总长度为2777个字符。

WITH aux AS (
SELECT DISTINCT
to_char(agr_name) AS agr_name
FROM
dat_skills
)
SELECT
count(1), sum(length(agr_name))
FROM
aux

我可以绕过这个限制吗?还是我做错了什么?

Answer 1

你没有做错任何事情(可能除了计算，因为你必须添加单引号和"as";它被添加到查询返回的每个值中)，但是LISTAGG的上限设置为4000个字符。

该怎么办?切换到XMLAGG。如何?这样的:

SQL> SELECT LISTAGG (dname, ', ') WITHIN GROUP (ORDER BY dname) AS r_listagg,
2         --
3         RTRIM (
4            XMLAGG (XMLELEMENT (e, dname || ', ') ORDER BY dname).EXTRACT (
5               '//text()'),
6            ', ') r_xmalagg
7    FROM dept
8  /
R_LISTAGG
--------------------------------------------------------------------------------
R_XMALAGG
--------------------------------------------------------------------------------
ACCOUNTING, OPERATIONS, RESEARCH, SALES
ACCOUNTING, OPERATIONS, RESEARCH, SALES

SQL>

重写它，使它适用于您的表&列。