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