How to resolve ORA-01489: result of string concatenation is too long

Question

I have a table temp and Im trying to query as below :

    SELECT
    LISTAGG( 'MAX(CASE WHEN CATEGORY = '''||CATEGORY||''' THEN "'||"LEVEL"||'" END) AS "'||
    "LEVEL"||'_'||CATEGORY||'"' , ',' ) WITHIN GROUP ( ORDER BY CATEGORY, "LEVEL" DESC
    ) AS col2
        FROM
    (
        SELECT DISTINCT
            "LEVEL",
            CATEGORY
        FROM
            TEMP );

`

I get error as [Code: 1489, SQL State: 72000] ORA-01489: result of string concatenation is too long Im unable to get rid of this error. I'm using SQL Commander of DBVisualizer . I also tried to declare variable before but it does not seem to work:

@ECHO ${col2 ||32767||varchar2}$

I tried to ALTER SYSTEM SET MAX_STRING_SIZE = EXTENDED; which is also giving error : [Code: 2065, SQL State: 42000] ORA-02065: illegal option for ALTER SYSTEM.

Is there anything wrong in the code front if not what could be the workaround for this

Answer 1

If LISTAGG won't work (as, obviously, resulting string is longer than 4000 characters), switch to not-that-elegant XMLAGG which doesn't have that restriction. Result should be the same (compare these two):

SQL> select listagg(dname, ',') within group (order by dname) result
  2  from dept;

RESULT
--------------------------------------------------------------------------------
ACCOUNTING,OPERATIONS,RESEARCH,SALES


SQL> select rtrim(xmlagg(xmlelement(e, dname ||',') order by dname).extract
  2           ('//text()'), ',') result
  3  from dept;

RESULT
--------------------------------------------------------------------------------
ACCOUNTING,OPERATIONS,RESEARCH,SALES

SQL>