0

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

Justin Cave
  • 227,342
  • 24
  • 367
  • 384
Scope
  • 727
  • 4
  • 15
  • 1
    I believe you indicated in a previous question that you don't expect to have more than 12 columns which, if I understand, means that you don't expect to have more than 4 distinct `category` values. If that is the case, it doesn't make sense that the `listagg` result would exceed 4000 characters. Do you have some reason to believe that the result should exceed 4000 characters? What does `select distinct category from temp` return? – Justin Cave Oct 03 '20 at 09:53
  • hi check this for the output :https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=70bf221ab6eae07d26e8fec7d335b320. Im unable to run using dbvisualizer due to this ERROR : ORA-01489: result of string concatenation is too long ORA-06512: at line 15 – Scope Oct 03 '20 at 09:59
  • Max `Sets` I would have in a `temp` table would be 15. – Scope Oct 03 '20 at 10:00
  • I'm confused. If I uncomment the `open v_recordset for v_sql` line (as well as the declaration of `v_recordset`), I'm not getting an error in dbfiddle. Can you reproduce the error you're seeing in dbfiddle? – Justin Cave Oct 03 '20 at 10:04
  • Error is only when you add more more columns . – Scope Oct 03 '20 at 10:28
  • This is the ERROR : ORA-01489: result of string concatenation is too long ORA-06512 – Scope Oct 03 '20 at 10:28
  • DBFIDDLE doesnt show an error as such but does not give output more than 6 columns. Check https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=ecb758e82150064695d1f122cfb910e7 while I want all the 12 columns . – Scope Oct 03 '20 at 10:37
  • 1
    It does if you copy the query that is built in the prior step and run that as the last step https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=e1cdfc3bf626e18c5d3c3edfd6a4a5d7 – Justin Cave Oct 03 '20 at 10:41
  • Which one are you suggestin to run first, I didnt get you on this – Scope Oct 03 '20 at 10:45
  • I provided a link to a dbfiddle where I made the change in my prior comment. You'd need to take the SQL statement that is built up and printed out and then run that statement as the last step. – Justin Cave Oct 03 '20 at 10:47
  • OK. When I run `v_sql` directly it runs without giving an error, but on dbviz 10 im getting the above said error. I want it to work on my local dbviz – Scope Oct 03 '20 at 10:52
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/222446/discussion-between-scope-and-justin-cave). – Scope Oct 03 '20 at 11:25
  • ```http://www.br8dba.com/tag/alter-system-set-max_string_sizeextended-scopespfile/``` try these instructions for extending varchar2 size. – Aman Singh Rajpoot Oct 04 '20 at 06:37

1 Answers1

0

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>
Littlefoot
  • 131,892
  • 15
  • 35
  • 57