CODEWITHSUNDEEP
sqloracle-databasestring-aggregation
RaZzLe
RaZzLe

Reputation: 2119

XMLAGG Throws Inconsistent Datatypes: Expected - got CLOB

In the first place, my query (actually subquery, continue on reading) contains a LISTAGG, but I need to replace it with XMLAGG when users are started to get result of string concatenation is too long errors.

So, here is my subquery. I'm not sharing the whole query, because first it is too long and second its not so relevant with what I'm asking. I'll provide the data types nevertheless.

(select rtrim((xmlagg(xmlelement(e, sa1.answer || ',') order by sa1.answer).EXTRACT('//text()')).getclobval(), ',')
 from social_feed_answer sa1 where sa1.docid = f.id and sa1.issent = 1 and sa1.answertype in
 ('Reply', 'PrivateMessage', 'ApproveFeed','RejectFeed') and sa1.isactive = 1) as Answers,

Before your suggestions, I have already tried to remove RTRIM and ORDER BY clauses. None of them helps. I got different types of error e.g. Conversion of special character to escaped character failed or Character string buffer too small error.

My oracle version is 19c and that sa1.answer is nvarchar(2000).

I saw that this query works on other people flawless, but cannot figure out what's the case for me.

Thank you in advance.

Upvotes: 0

Views: 1408

Answers (2)

RaZzLe
RaZzLe

Reputation: 2119

(select xmlagg(xmlelement(e, REGEXP_REPLACE(sa1.answer, '[^[:print:]]', '') || ' , ') 
order by sa1.answer).EXTRACT('//text()') from social_feed_answer sa1  where sa1.docid = f.id and sa1.issent = 1 and sa1.answertype in 
('Reply', 'PrivateMessage', 'ApproveFeed', 'RejectFeed') and sa1.isactive = 1) as Answers

Above query works out without any issue.

REGEXP_REPLACE function is necessary only if your are facing ORA-64451: Conversion of special character to escaped character failed error. And, have to give up RTRIM function as this function converts the result type to VARCHAR2 which might makes you end up with result of string concatenation is too long error (by the way, getClobVal() right after XMLAGG didn't help).

Upvotes: 0

MT0
MT0

Reputation: 167972

One issue appears to be that sa1.answer || ',' raises an exception and not that there is a problem with XMLAGG. To fix that you can use EMPTY_CLOB() || sa1.answer || ',' or TO_CLOB(sa1.answer) || ',' so that the first value is a CLOB, and not a NVARCHAR2, and then the concatenated string will not be too large.

select rtrim(
         xmlagg(
           xmlelement(e, EMPTY_CLOB() || sa1.answer || ',')
           order by sa1.answer
         ).EXTRACT('//text()').getclobval(),
         ','
       )
from   social_feed_answer sa1
-- ...

or:

select rtrim(
         xmlagg(
           xmlelement(e, TO_CLOB(sa1.answer) || ',')
           order by sa1.answer
         ).EXTRACT('//text()').getclobval(),
         ','
       )
from   social_feed_answer sa1
-- ...

db<>fiddle here

Upvotes: 0

Related Questions