Getting this error "result of string concatenation is too long"

Kannan Sekar

I need to do concatenate a name column based on id column. The concatenate string contains more than 4000 characters, so i'm getting this error "result of string concatenation is too long".. Please help me how to avoid this error..

CREATE TABLE T1 (ID NUMBER, NAME VARCHAR2(250));

INSERT INTO T1 (ID, NAME) VALUES (100, 'ASDFG');

INSERT INTO T1 (ID, NAME) VALUES (100, 'ASDFGHJHFRRD');

INSERT INTO T1 (ID, NAME) VALUES (100, 'QWERTYUIKJHGFDSAFGHYT');

output like:

--------------

id name

100 ASDFG, ASDFGHJHFRRD, QWERTYUIKJHGFDSAFGHYT.............................

RogerT

First of all ... how do you do the concatenation?

• Listagg
• Your Own Function
• Within a Cursor (PL/SQL)?

Basically the easiest way (if you are not using listagg) is to create a clob instead of a varchar2.

hth

Alex Nuijten

In addition to what RogerT said, you could also used extended datatypes (increases the limitation of VARCHAR2 to 32767) or use "ON OVERFLOW TRUNCATE" with LISTAGG

Kannan Sekar

to RogerT:

Thank you for your response.. I tried below options but its not working..

1. Listagg
2. create my own function using cursor. while executing function getting error "character string buffer too small".

Kannan Sekar

to Alex,

Thank you for your reponse.. I'm using 11g version. so i couldn't use ON OVERFLOW TRUNCATE..

BluShadow

"avoiding the error" depends on what you want it to do. What do you want to happen if the concatenated strings are more than 4000 characters?

If you don't have a verison of the database where you can extend varchar2 to 32767 characters (version 12c onwards) then assuming you want to keep all the data you're concatenating, your only options would be a) concatenate into a clob datatype instead, or b) don't concatenate the data and leave it stored in multiple rows.

It's hard to help if you don't actually say what you want the logic to be (saying you want to avoid the error doesn't tell us what you actually expect to happen) and if you don't show the code you're using so we can see how you're going about your concatenation.