Oracle error: ORA-01489: result of string concatenation is too long.

In my Oracle 11gR2 database I a writing this query (attached in code).

But I get this error:
I get error: ORA-01489: result of string concatenation is too long.

I put a substr function to limit the LISTAGG value to 2000 but still I get the error.

This query works when the concatenated length is not huge...

Is there any way to fix it?
SELECT x.f1 as f1, substr((LISTAGG(x.f1, ', ') WITHIN GROUP(ORDER BY x.id)), 1, 2000) as f2_list
              FROM (SELECT t.id as id, t.f1 as f1, t.f2 as f2
                      FROM MYTAB t,
                       ) x
                      GROUP BY f1

Open in new window

toookiAsked:
Who is Participating?
 
sdstuberConnect With a Mentor Commented:
it's not too hard,  the type and function are a one time effort,
afterward you can use them as shown below


CREATE OR REPLACE TYPE vcarray as table of varchar2(4000);

CREATE OR REPLACE FUNCTION tbl2str(p_tbl IN vcarray, p_delimiter IN VARCHAR2 DEFAULT ',')
    RETURN VARCHAR2
    DETERMINISTIC
IS
    v_str   VARCHAR2(32767);
    v_index INTEGER := 2;
BEGIN
    IF p_tbl.COUNT > 0
    THEN
        v_str := p_tbl(1);

        WHILE v_index <= p_tbl.COUNT AND LENGTH(v_str) <= 2000
        LOOP
            v_str := v_str || p_delimiter || p_tbl(v_index);
            v_index := v_index + 1;
        END LOOP;
    END IF;

    RETURN SUBSTR(v_str, 1, 2000);
END;
/

SELECT x.f2 AS f2,
        tbl2str(cast(collect(dbms_lob.substr(x.f3,2000,1) order by x.f1) as vcarray))      
           AS f3_list
  FROM (SELECT t.f1 AS f1, t.f2 AS f2, t.f3 AS f3
          FROM tab1 t) x
GROUP BY x.f2
;
0
 
sdstuberCommented:
you can't get around the varchar2 limit using LISTAGG,  because LISTAGG returns a varchar2

you can use xml aggregation to return a clob though


SELECT x.f1 AS f1,
       SUBSTR(
           RTRIM(
               EXTRACT(
                   XMLAGG(XMLELEMENT("x", x.f1 || ',') ORDER BY x.id),
                   '/x/text()'
               ).getclobval(),
               ','
           ),
           1,
           2000
       )
           AS f2_list
  FROM (SELECT t.id AS id, t.f1 AS f1, t.f2 AS f2
          FROM mytab t) x
GROUP BY f1
0
 
slightwv (䄆 Netminder) Commented:
The substr call is only made AFTER the listagg call completes.

If listagg will not return a CLOB (the docs will confirm this.  I'm on mobile and cannot look up), you will need a different approach.

I know the XML method will return a CLOB.
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
toookiAuthor Commented:
Thank you all. Ok,, I cannot use listagg  then.

I tried the query. It runs -- the second column comes as CLOB. But the CLOB colum content is showing empty.
I tried with one sample row. The listagg gives the correct comma-separated value for that.
Cannot we do to_char method on this CLOB to get a varchar2 value in the output for the 2nd field...?

Thanks.

SELECT x.f1 AS f1,
       SUBSTR(
           RTRIM(
               EXTRACT(
                   XMLAGG(XMLELEMENT("x", x.f1 || ',') ORDER BY x.id),
                   '/x/text()'
               ).getclobval(),
               ','
           ),
           1,
           2000
       )
           AS f2_list
  FROM (SELECT t.id AS id, t.f1 AS f1, t.f2 AS f2
          FROM mytab t) x
GROUP BY f1

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
A varchar2 outside pl/sql code has a maximum of 4000 characters in a single byte charaterset.

Any more than that, you need a clob.

For your empty clob, double check your tables/columns.
0
 
toookiAuthor Commented:
Thank you.
I used this query but I do not get any output.
I tested by inserting as well but I see empty.
I have attached the exact code.
CREATE TABLE TAB1 
   (  f1 NUMBER(18,0), 
      f2 VARCHAR2(255), 
      f3 CLOB
   ) ;
   
   
insert into TAB1 values
( 1, 'X123', 'val1'); 

insert into TAB1 values
( 2, 'X123', 'val2'); 

insert into TAB1 values
( 3, 'X123', 'val3'); 


commit;

SELECT x.f2 AS f2,
       SUBSTR(
           RTRIM(
               EXTRACT(
                   XMLAGG(XMLELEMENT("x", x.f3 || ',') ORDER BY x.f1),
                   '/x/text()'
               ).getclobval(),
               ','
           ),
           1,
           2000
       )
           AS f3_list
  FROM (SELECT t.f1 AS f1, t.f2 AS f2, t.f3 AS f3
          FROM TAB1 t) x
GROUP BY x.f2

Open in new window

0
 
sdstuberCommented:
I based my xmlagg version on your original query but I wonder if you intended to aggregate f2 instead of f1
0
 
slightwv (䄆 Netminder) Commented:
I had to add a ';' or '/' at the end of your select but using 10.2.0.1 and sqlplus I just ran your code as-is and received:

F2
-------------------------------------------------------------------------------

F3_LIST
-------------------------------------------------------------------------------

X123
val1,val2,val3


SQL>

0
 
sdstuberCommented:
sorry cross posted above.  the problem might be in how you are handling the resulting clob result

SUBSTR will return a CLOB

try casting instead


SELECT x.f2 AS f2,
       CAST(
           RTRIM(
               EXTRACT(XMLAGG(XMLELEMENT("x", x.f3 || ',') ORDER BY x.f1), '/x/text()').getclobval(),
               ','
           ) AS VARCHAR2(2000)
       )
           AS f3_list
  FROM (SELECT t.f1 AS f1, t.f2 AS f2, t.f3 AS f3
          FROM tab1 t) x
GROUP BY x.f2
0
 
sdstuberCommented:
also note,  if your f3  CLOB column contains large strings greater than varchar2 limit,  the xmlagg will skip them
0
 
toookiAuthor Commented:
Thanks a lot.

But I do not know it is showing empty for F3_LIST column for me. Mine is 11gR2.
 screen1 screen2 screen3
0
 
sdstuberCommented:
please use text copy-paste instead of images,  much easier to read, and should be easier for you too
0
 
sdstuberCommented:
hmm, works for me on 11gR2
0
 
toookiAuthor Commented:
Thanks.
I have attached the text copy....
SQL> 
SQL> CREATE TABLE TAB1
  2     (  f1 NUMBER(18,0),
  3        f2 VARCHAR2(255),
  4        f3 CLOB
  5     ) ;
 
Table created
SQL> insert into TAB1 values
  2  ( 1, 'X123', 'val1');
 
1 row inserted
SQL> insert into TAB1 values
  2  ( 2, 'X123', 'val2');
 
1 row inserted
SQL> insert into TAB1 values
  2  ( 3, 'X123', 'val3');
 
1 row inserted
SQL> commit;
 
Commit complete

SQL> 
SQL> SELECT x.f2 AS f2,
  2         CAST(
  3             RTRIM(
  4                 EXTRACT(XMLAGG(XMLELEMENT("x", x.f3 || ',') ORDER BY x.f1), '/x/text()').getclobval(),
  5                 ','
  6             ) AS VARCHAR2(2000)
  7         )
  8             AS f3_list
  9    FROM (SELECT t.f1 AS f1, t.f2 AS f2, t.f3 AS f3
 10            FROM tab1 t) x
 11  GROUP BY x.f2
 12  ;
 
F2                                                                               F3_LIST
-------------------------------------------------------------------------------- --------------------------------------------------------------------------------
X123                                                                             
 


SQL> 
SQL> SELECT x.f2 AS f2,
  2         SUBSTR(
  3             RTRIM(
  4                 EXTRACT(
  5                     XMLAGG(XMLELEMENT("x", x.f3 || ',') ORDER BY x.f1),
  6                     '/x/text()'
  7                 ).getclobval(),
  8                 ','
  9             ),
 10             1,
 11             2000
 12         )
 13             AS f3_list
 14    FROM (SELECT t.f1 AS f1, t.f2 AS f2, t.f3 AS f3
 15            FROM TAB1 t) x
 16  GROUP BY x.f2
 17  ;
 
F2                                                                               F3_LIST
-------------------------------------------------------------------------------- --------------------------------------------------------------------------------
X123                                                                             
 
SQL>

Open in new window

0
 
toookiAuthor Commented:
It is actually is even better for me to get the truncated varchar2 output (as in the second query) if the aggregated string exceeds 2000 length...
But do not know I am getting empty f3_list .
0
 
sdstuberCommented:
since you don't really want clob results anyway....
try this...


SELECT x.f2 AS f2,
       CAST(
           RTRIM(
               EXTRACT(XMLAGG(XMLELEMENT("x", dbms_lob.substr(x.f3,2000,1) || ',') ORDER BY x.f1), '/x/text()').getclobval(),
               ','
           ) AS VARCHAR2(2000)
       )
           AS f3_list
  FROM (SELECT t.f1 AS f1, t.f2 AS f2, t.f3 AS f3
          FROM tab1 t) x
GROUP BY x.f2
0
 
sdstuberCommented:
>>> actually is even better for me to get the truncated varchar2 output

I'm not sure what you're trying to say there, but I think I got that in the previous post
0
 
toookiAuthor Commented:
Many thanks sdstuber.
Actually most aggregated field values will be less than 2000 length. Only a few are causing the issue. So for those if I get the truncated (first 2000) -- it will be great.
Yes I need to get the o/p in varchar2 .

I tried the last query you sent. But still getting null result..
SQL> SELECT x.f2 AS f2,
  2         CAST(
  3             RTRIM(
  4                 EXTRACT(XMLAGG(XMLELEMENT("x", dbms_lob.substr(x.f3,2000,1) || ',') ORDER BY x.f1), '/x/text()').getclobval(),
  5                 ','
  6             ) AS VARCHAR2(2000)
  7         )
  8             AS f3_list
  9    FROM (SELECT t.f1 AS f1, t.f2 AS f2, t.f3 AS f3
 10            FROM tab1 t) x
 11  GROUP BY x.f2;
 
F2                                                                               F3_LIST
-------------------------------------------------------------------------------- --------------------------------------------------------------------------------
X123                                                                             
 
SQL>

Open in new window

0
 
sdstuberCommented:
are you sure your list is NULL?  any chance you have a bunch of concatenated blanks?

do you have other data than just the sample data above?
0
 
toookiAuthor Commented:
Thanks. But yes it is null. I even put it in a new table and query.

Maybe it is for some Oracle version matter...?
SQL> CREATE TABLE TAB2
  2     (  P1 VARCHAR2(256),
  3        p2 VARCHAR2(2000)
  4     ) ;
 
Table created
SQL> insert into TAB2 (p1, p2)
SQL> (SELECT x.f2 AS p1,
  2         CAST(
  3             RTRIM(
  4                 EXTRACT(XMLAGG(XMLELEMENT("x", dbms_lob.substr(x.f3,2000,1) || ',') ORDER BY x.f1), '/x/text()').getclobval(),
  5                 ','
  6             ) AS VARCHAR2(2000)
  7         )
  8             AS p2
  9    FROM (SELECT t.f1 AS f1, t.f2 AS f2, t.f3 AS f3
 10            FROM tab1 t) x
 11  GROUP BY x.f2);
 
P1                                                                               P2
-------------------------------------------------------------------------------- --------------------------------------------------------------------------------
X123                                                                             
SQL> select * from tab2 where p2 is null;
 
P1                                                                               P2
-------------------------------------------------------------------------------- --------------------------------------------------------------------------------
 
SQL>

Open in new window

0
 
sdstuberCommented:
that's very odd you might might need to contact oracle support.


Do you have authority to create types and functions?  if so, there is an alternate way
0
 
toookiAuthor Commented:
No I dont have Oracle support..
Yes I can create type and functions.... will that be very difficult way ...?
0
 
toookiAuthor Commented:
Many thanks sdstuber, that works.

One question, I use a similar function that you sent to me before. I attached it. That function is quite similar and I use it for other purpose. Can I use that function for this query?
CREATE OR REPLACE TYPE vcarray as table of varchar2(4000);
CREATE OR REPLACE FUNCTION STR2TAB(p_string    IN VARCHAR2,
                                   p_delimiter IN VARCHAR2 := ',')
  RETURN vcarray
  PIPELINED
 AS
  v_length NUMBER := LENGTH(p_string);
  v_start  NUMBER := 1;
  v_index  NUMBER;
BEGIN
  WHILE (v_start <= v_length) LOOP
    v_index := INSTR(p_string, p_delimiter, v_start);

    IF v_index = 0 THEN
      PIPE ROW(SUBSTR(p_string, v_start));
      v_start := v_length + 1;
    ELSE
      PIPE ROW(SUBSTR(p_string, v_start, v_index - v_start));
      v_start := v_index + 1;
    END IF;
  END LOOP;

  RETURN;
END;

Open in new window

0
 
sdstuberCommented:
no they are related, but not interchangeable

 tbl2str is the reverse  of str2tab

tbl2str  takes a collection and iterates through it to create a string

str2tab takes a string, interates through it and creates a collection
0
 
sdstuberCommented:
the tbl2str in this thread is modified to ensure it never returns more than 2000 characters for this question


to change the limit,  change 2000 here

       AND LENGTH(v_str) <= 2000

and  here

      RETURN SUBSTR(v_str, 1, 2000);
0
 
toookiAuthor Commented:
Many thanks sdstuber for the explanation.
Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.