SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Limit Listtag maximum length to less than 4000 chars and proceed with out ORA-01489

1002123Sep 29 2017 — edited Oct 2 2017

Hi,

I am using the oracle:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

PL/SQL Release 11.2.0.4.0 - Production

I have a need to concatenate the column values from multiple rows and am using LISTAGG but am getting " ORA-01489: result of string concatenation is too long". The reason is because for one group the maximum length exceeds 4000 characters.

MY LISTTAG statement:

LISTAGG(R_PLAN, ',' ) WITHIN GROUP (ORDER BY NULL)

I can overcome the character length limit by using below but it is way too slow.

rtrim(xmlagg(XMLELEMENT(e,R_PLAN,',').EXTRACT('//text()') ).GetClobVal(),',')

So am seeking help here to find out quicker ways to limit the character length to 4000 and am fine if the concatenated result is restricted/truncated to have to less than 4000 characters but the query should not fail with ORA-01489 error because for fewer groups the entire query is failing.

This post has been answered by 1002123 on Oct 1 2017

Jump to Answer

Locked Post

New comments cannot be posted to this locked post.

Locked on Oct 30 2017

Added on Sep 29 2017

15 comments

11,747 views