Skip to Main Content
  • Questions
  • How to concatenate string having length greater than 4000 characters

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, devendra.

Asked: December 12, 2017 - 1:24 pm UTC

Last updated: January 17, 2023 - 1:42 am UTC

Version: Oracle 12c.2

Viewed 10K+ times! This question is

You Asked

I am using xmlagg function to concatenate records with comma separated values. But i am getting error when columns count is than 300

I getting below error

Error starting at line : 8 in command -
select 
   rtrim (xmlagg (xmlelement (e, COLUMN_NAME || ',')).extract ('//text()'), ',') COLUMN_NAMES
   from user_tab_columns where TABLE_NAME = 'REFCLIENT'

Error report -
ORA-19011: Character string buffer too small


Is there any other alternative to address this error??

and Chris said...

You have a couple of options:

1. Set the max_string_size parameter to extended
2. Avoid the implicit conversion to a varchar2 by extracting the data as clob

max_string_size

Starting in 12.1, you can increase the maximum length of varchar2 in SQL to 32,767. To do this, you need to set max_string_size = extended.

It's not quite as straightforward as running an alter system. For instructions on how to do this, read:

https://oracle-base.com/articles/12c/extended-data-types-12cR1

Extract as clob

Of course, all using extended data types really does is move the point at which you hit the error. To avoid it completely, convert the output of xmlagg to a clob explicitly by calling getClobVal:

select rtrim( 
         xmlagg( 
           xmlelement( e,column_name || ',' ) 
         ).extract( '//text()').getClobVal(),
         ',' 
       ) column_names
from   user_tab_columns;

COLUMN_NAMES                                                                       
DOC,CITY,OLYMPIC_YEAR,SPORT,DISCIPLINE,ATHLETE,NOC,GENDER,EVENT,EVENT_GENDER,...


Or, if you just want to avoid the error and don't need the whole string, you could use the "on overflow truncate" clause of listagg in 12.2:

select listagg(column_name, ',' on overflow truncate) 
         within group (order by table_name, column_id) cols
from   user_tab_columns;

COLS                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
ID,JUNK,ID,I,A,B,ID,NAME,TOY_ID,TOY_NAME,COLOUR,IS_DELETED,QUEUE,MSG_ID,...

Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

devendra Gotmare, December 13, 2017 - 11:19 am UTC

Hi Chris Saxon,
Thanks for the prompt and quick response.
Your response serves the purpose.
Great Work.
thanks a lot

Error when using XMLAGG

Raja, July 01, 2021 - 10:34 am UTC

Hi All,

I used the XMLAGG function and got below error,

[Error] Execution (2: 87): ORA-31011: XML parsing failed
ORA-19213: error occurred in XML processing at lines 1
LPX-00216: invalid character 243 (0xF3)

And I followed the above instruction of using getClobVal() to get rid the error, but now I am getting another error as below,

[Error] Execution (2: 2): ORA-00932: inconsistent datatypes: expected - got CLOB

Could any one give idea to execute the XMLAGG function successfully?

Thanks,
Raja


Chris Saxon
July 02, 2021 - 10:57 am UTC

Please give an example of the query you're running and the XML document

Error when using XMLAGG

Mary, July 28, 2021 - 12:27 pm UTC

Hi, thanks for very useful response to the initial question. My comment is to the newest comment made by Raja.
I believe I have the same and it is due to the character 'e' in the query you provided. I am running this in python file, so it fails right away, because it is unknown. What would you recommend?

from sqlalchemy import func

# Here I detect that the engine is oracle and trying to execute the following
agg_func = func.rtrim(func.xmlagg(func.xmlelement(e, column, '|')).extract('//text()').getclobval(),'|')


Chris Saxon
July 28, 2021 - 12:50 pm UTC

xmlelement(e, column)

creates an XML tag named E, e.g.

<E>test</E>

What exactly is the document you're processing and what error do you get?

ORA-19011: Character string buffer too small

Gokul Patil, January 16, 2023 - 6:34 pm UTC

this solution to my problem was awesome. I was working on high priority issue and provided SQL query was solved my requirement. Thank you so much !!.
Connor McDonald
January 17, 2023 - 1:42 am UTC

Glad we could help

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.