Forum Stats

  • 3,768,285 Users
  • 2,252,771 Discussions
  • 7,874,515 Comments

Discussions

ORA-01489: due to large text

Amiva
Amiva Member Posts: 241
edited Jul 17, 2019 3:27AM in SQL & PL/SQL

ORA-01489: result of string concatenation is too long

01489. 00000 -  "result of string concatenation is too long"

*Cause:    String concatenation result is more than the maximum size.

*Action:   Make sure that the result is less than the maximum size

Getting error message when i run update query . Please check below.

---source table

create table docs (id number , text varchar2(80));

insert into docs values(1, 'this is: the 1st line of the first document');

insert into docs values(1, 'this is: the 2nd line of the first document');

insert into docs values(1, 'this is: the 3rd line of the first document');

insert into docs values(1, 'this is: the 1st line of the first document');

insert into docs values(1, 'this is: the 2nd line of the first document');

insert into docs values(1, 'this is: the 3rd line of the first document');

insert into docs values(1, 'this is: the 1st line of the first document');

insert into docs values(1, 'this is: the 2nd line of the first document');

insert into docs values(1, 'this is: the 3rd line of the first document');

insert into docs values(1, 'this is: the 1st line of the first document');

insert into docs values(1, 'this is: the 2nd line of the first document');

insert into docs values(1, 'this is: the 3rd line of the first document');

insert into docs values(1, 'this is: the 1st line of the first document');

insert into docs values(1, 'this is: the 2nd line of the first document');

insert into docs values(1, 'this is: the 3rd line of the first document');

insert into docs values(1, 'this is: the 1st line of the first document');

insert into docs values(1, 'this is: the 2nd line of the first document');

insert into docs values(1, 'this is: the 3rd line of the first document');

insert into docs values(1, 'this is: the 1st line of the first document');

insert into docs values(1, 'this is: the 2nd line of the first document');

insert into docs values(1, 'this is: the 3rd line of the first document');

insert into docs values(1, 'this is: the 1st line of the first document');

insert into docs values(1, 'this is: the 2nd line of the first document');

insert into docs values(1, 'this is: the 3rd line of the first document');

insert into docs values(1, 'this is: the 1st line of the first document');

insert into docs values(1, 'this is: the 2nd line of the first document');

insert into docs values(1, 'this is: the 3rd line of the first document');

insert into docs values(1, 'this is: the 1st line of the first document');

insert into docs values(1, 'this is: the 2nd line of the first document');

insert into docs values(1, 'this is: the 3rd line of the first document');

insert into docs values(2, 'this is: the 1st line of the second document');

insert into docs values(2, 'this is: the 2nd line of the second document');

------ Like that i have thousands of rows belongs to Id 1.

----Target table and expected output to have each id with corresponding text .

create table target_doc (id number, text clob);

insert into target_doc values(1,'');

insert into target_doc values(2,'');

commit;

update target_doc t set text=(select  REPLACE(LISTAGG(d.text, '; ') WITHIN GROUP (ORDER BY text), ';',  chr(13) || chr(10))  from docs d where d.id=t.id

group by d.id)

where exists (select  1 from docs d where d.id=t.id group by d.id);

i would like to keep the text in single row for each id usng above update.

getting an error if i have more text per id.

ORA-01489: result of string concatenation is too long

01489. 00000 -  "result of string concatenation is too long"

*Cause:    String concatenation result is more than the maximum size.

*Action:   Make sure that the result is less than the maximum size

Mustafa_KALAYCI

Answers

  • Arun Kumar Gupta
    Arun Kumar Gupta Member Posts: 996 Gold Badge
    edited Jul 17, 2019 2:08AM

    See the reason for error and approach to overcome the issue below.

    ORA-01489 with listagg

    Regards

    Arun

  • AndrewSayer
    AndrewSayer Member Posts: 12,998 Gold Crown
    edited Jul 17, 2019 3:27AM

    The obvious question is why do you want to store the data like that? Are you going to make sure target_doc always has the same information as doc?

    Listagg errors when it hits 4000 characters, it was designed for varchar2. In 12.2, an enhancement was made to allow it to truncate anything larger than 4000 characters but if you need that data that’s not going to be appropriate.

    There is no built in listagg for clobs, but you can very easily loop over your table and append into a clob.

    (Untested so expect typos)

    Declare

    cClob clob;

    begin

    for rDoc in (select distinct id from doc) loop

    cClob :='';

    for rPiece in (select text from doc where doc.id =rDoc.id) loop

    cClob := cClob||chr(13)||chr(10)||rPiece.text);

    end loop;

    cClob := rtrim(cClob,chr(13)||chr(10));

    update target_doc

    set text = cClob

    where id = rDoc.id;

    end loop;

    end;

    It might be more efficient to get the lob locator from target_doc and use dbms_lob to append values to it

    Mustafa_KALAYCIAmiva