Forum Stats

  • 3,767,857 Users
  • 2,252,726 Discussions
  • 7,874,365 Comments

Discussions

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

Kannan Sekar
Kannan Sekar Member Posts: 51 Blue Ribbon

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.............................

Tagged:

Answers

  • RogerT
    RogerT Member Posts: 1,855 Gold Trophy

    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
    Alex Nuijten Member Posts: 179 Silver Badge

    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
    Kannan Sekar Member Posts: 51 Blue Ribbon

    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
    Kannan Sekar Member Posts: 51 Blue Ribbon

    to Alex,

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

  • BluShadow
    BluShadow Member, Moderator Posts: 41,470 Red Diamond

    "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.