asked on

Oracle - WM_Concat Error: String Buffer too small

Hi,

I need to concatenate a list of directory paths into a single field per object. However, I am getting the buffer error mentioned in the subject.

Any ideas in helping me to get around this? I understand the varchar2 size limitation, so I guess what I am asking is...

Is it possible to concatenate the data and stop before it reaches the varchar2 limit? This scenario will not happen often and data truncation is ok if it does.

I presume I will need a new custom function, but something that does not kill performance.

TIA,

Iain

SELECT Name,
Server,
wm_concat(ExclusionList) AS ExclusionList
FROM
(
SELECT DISTINCT
Server,
Name,
CONCAT(Directory,Arguments) AS ExclusionList
FROM TABLE
)T1
GROUP BY Name,
Server

Sean Stuber

wm_concat is an undocumented and unsupported function.

in 11g you should use listagg

in 10g you should use a user defined aggregate like stragg (search here or asktom), xml aggregation or COLLECT with a function to convert the nested table to a string.

all of the above suffer from the 4000 character limit except the last two
xml aggregation can return a clob instead of a varchar2, and if you write your own function for the nested table conversion you can return whatever you want, including a clob

Sean Stuber

example can be found here...

https://www.experts-exchange.com/questions/26246671/WM-CONCAT.html

Sean Stuber

the link above references this question for an xml aggregation example.

https://www.experts-exchange.com/questions/24914739/In-Oracle-how-can-I-concatenate-multiple-results-returned-from-select-statement-into-a-comma-separated-string.html

if you follow that, use "getclobval()" instead of "getstringval()" to return a clob instead of varchar2

Sean Stuber

you could easily modify the tbl2str function in the first link to return a clob instead of a varchar2

mullykid

ASKER

Thanks. I am using 10g.

Ok, so you suggest something like this?

SELECT column1,
column2,
RTRIM(
EXTRACT(XMLAGG(XMLELEMENT("s", yourtable.column3 || ',')), '/s/text()').getstringval(),
','
)
column3
FROM yourtable
GROUP BY column1, column2

Sean Stuber

yes, with getclobval() instead of getstringval()

mullykid

ASKER

Hi,

I replaced the function call and I get an error 'Inconsistent data types'

Is there something else I need to replace?

TIA

mullykid

ASKER

Does 'yourtable.column3' field need to be a CLOB?

mullykid

ASKER

I also got the inconsistent data type error when using tbl2str to return a CLOB.

mullykid

ASKER

Hi,

Ok, I got it 80% working using...

RTRIM(xmlagg( XMLELEMENT( ";", concat(Directory,Arguments)) ).GETCLOBVAL()) as Exclusion_List

Now I need to remove the XML tags. EXTRACT doesn't work due to the type inconsistency error I mentioned above.

How do I remove these tags?

Sean Stuber

what are your column types?

mullykid

ASKER

Directory and Arguments are both VARCHAR2

ASKER CERTIFIED SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Sean Stuber

here is the sample data I used to test with...

WITH yourtable AS (SELECT 'serv' server,
                          'n'name,
                          'dir' || LEVEL directory,
                          'arg' || LEVEL arguments
                     FROM DUAL
                   CONNECT BY LEVEL < 400)

Open in new window

mullykid

ASKER

Thanks very much. One final question. Please confirm that the value being returned in 'exclusionlist' is of type CLOB?

Sean Stuber

if you use .getclobval() then yes it will be of type clob

if you use .getstringval() then no it will be of type varchar2