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
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?