时间戳

首页 > 解决方案 > Oracle 字符串连接太长

sql - Oracle 字符串连接太长

问题描述

我正在尝试执行以下查询，该查询通过将值传递给变量来对表进行透视。它适用于其他查询。

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

有什么解决方法可以绕过这个限制吗？或者我做错了什么？

标签： sqloracle

解决方案

您没有做错任何事情（可能除了计算，因为您必须添加单引号和“ 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>

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

推荐阅读