Solved

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

Posted on 2011-09-22
26
8,030 Views
Last Modified: 2012-05-12
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

0
Comment
Question by:toooki
  • 13
  • 10
  • 3
26 Comments
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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
 

Author Comment

by:toooki
Comment Utility
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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
 

Author Comment

by:toooki
Comment Utility
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
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
I based my xmlagg version on your original query but I wonder if you intended to aggregate f2 instead of f1
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
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
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
also note,  if your f3  CLOB column contains large strings greater than varchar2 limit,  the xmlagg will skip them
0
 

Author Comment

by:toooki
Comment Utility
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
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
please use text copy-paste instead of images,  much easier to read, and should be easier for you too
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
hmm, works for me on 11gR2
0
 

Author Comment

by:toooki
Comment Utility
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:toooki
Comment Utility
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
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
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
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
>>> 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
 

Author Comment

by:toooki
Comment Utility
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
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
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
 

Author Comment

by:toooki
Comment Utility
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
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
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
 

Author Comment

by:toooki
Comment Utility
No I dont have Oracle support..
Yes I can create type and functions.... will that be very difficult way ...?
0
 
LVL 73

Accepted Solution

by:
sdstuber earned 475 total points
Comment Utility
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
 

Author Comment

by:toooki
Comment Utility
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
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
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
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
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
 

Author Comment

by:toooki
Comment Utility
Many thanks sdstuber for the explanation.
Thanks!
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

This article describes some very basic things about SQL Server filegroups.
Read about achieving the basic levels of HRIS security in the workplace.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now