Solved

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

Posted on 2011-09-22
26
8,457 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
ID: 36584127
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)
ID: 36584128
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
ID: 36584256
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)
ID: 36584292
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
ID: 36584374
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
ID: 36584377
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)
ID: 36584428
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
ID: 36584436
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
ID: 36584444
also note,  if your f3  CLOB column contains large strings greater than varchar2 limit,  the xmlagg will skip them
0
 

Author Comment

by:toooki
ID: 36584904
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
ID: 36584909
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
ID: 36584922
hmm, works for me on 11gR2
0
 

Author Comment

by:toooki
ID: 36584926
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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 

Author Comment

by:toooki
ID: 36584938
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
ID: 36584940
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
ID: 36584943
>>> 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
ID: 36584961
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
ID: 36584966
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
ID: 36584991
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
ID: 36584995
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
ID: 36585003
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
ID: 36585025
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
ID: 36585053
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
ID: 36585058
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
ID: 36585066
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
ID: 36585216
Many thanks sdstuber for the explanation.
Thanks!
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

863 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

23 Experts available now in Live!

Get 1:1 Help Now