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??
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_sizeStarting 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 clobOf 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,...