Oracle String concatenation is too long

Question

I'm trying to execute the below query, which pivots a table by passing the values to pivot in a variable. It has worked for other queries.

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;

But I'm getting the below error:

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.

If I execute the select distinct it retrieves 138 rows with a total length of 2777 characters of length.

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

Is there any workaround I could take to bypass this restriction? Or something that I'm doing wrong?

score 0 · Answer 1 · answered Mar 12 '21 at 13:23

0

You aren't doing anything wrong (except probably the calculation, because you have to add single quotes and "as" which is added to every value query returns), but LISTAGG has the upper limit set to 4000 characters.

What to do? Switch to XMLAGG. How? Like this:

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>

Rewrite it so that it works for your tables & columns.

answered Mar 12 '21 at 13:23

Littlefoot

131,892
15
35
57

ORA-19011: Character string buffer too small 19011. 00000 - "Character string buffer too small" *Cause: The string result asked for is too big to return back *Action: Get the result as a lob instead I'm getting this after rewriting my query with xmlagg – Javi Torre Mar 12 '21 at 13:47
Also, I'm using the quotes and as "" because if I don't do that, the columns are not interpreted correctly. Not sure if there's any easier way to do this. – Javi Torre Mar 12 '21 at 13:48
Try to put the result into a CLOB datatype variable (instead of VARCHAR2). – Littlefoot Mar 12 '21 at 13:52
Error report - ORA-19011: Character string buffer too small ORA-06512: at line 24 19011. 00000 - "Character string buffer too small" *Cause: The string result asked for is too big to return back *Action: Get the result as a lob instead – Javi Torre Mar 12 '21 at 14:38
This is what I'm now getting after defining cols as a clob – Javi Torre Mar 12 '21 at 14:38
Could I reopen the question as the other question's answer is not working for me? – Javi Torre Mar 13 '21 at 08:49

Oracle String concatenation is too long

1 Answers1