on Jan 14, 2019Hi,
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?
It probably on runs in SQL dev because it only gets the first 50 rows by default. Actually if you try to export the whole output in SQL Dev you will most likely hit the same error.
It is more of an Oracle limitation that a single VARCHAR column cannot have more than 4000 characters. I would suggest you revisit your report logic to see if you really need this concatenation or how you can somehow fit your data into Oracle's 4000 character limitation.