AnsweredAssumed Answered

ORA-01489:result of string concatenation is too long error in reports

Question asked by Ranjan Bhat Employee on Jan 14, 2019
Latest reply on Jan 15, 2019 by Mostafa Helmy

Hi,

I have a SQL query where I am seeing error "Error validating FROM clause:ORA-01489:result of string concatenation is too long" if I run this in Report UI.
If I run this complete query in SQL Dev, it runs fine but in Report module if RSA IG&L, I see this error.
It has sections like below:

 

(select NOM,V1 from (select LISTAGG(RES,',') WITHIN GROUP (ORDER BY RES) as V1,NOM from(select distinct (c.CAS4) as res, AC.NAME as NOM from avuser.V_AV_OOR_EXPLODEDUSERENTS a
left join avuser.T_AV_ACCOUNTS AC
on a.ENTITLEd_id= AC.ID
left join avuser.T_ENTITLEMENT_GROUPS c
on a.entitlement_id=c.id
where a.APPLICATION_ID= (select ID from AVUSER.T_APPLICATIONS where lower(NAME)='trax') and AC.APPLICATION_ID = (select ID from AVUSER.T_APPLICATIONS where lower(NAME)='trax')
)
Group by NOM
))

 

I found out that solution is to apply XMLAGG instead of LISTAGG but it didn't help. The CAS4 in c.CAS4 above is a String type 4000char length custom attribute defined in Application Role Attribute of RSA IG&L.

Do you have a solution to this problem. Is there a restriction on the report as it is running fine in SQL Dev but not in Report UI?

Outcomes