Custom Report giving Error
Recently PPM is upgraded to 9.42 version in Stage Environment, We had issue with WM_Concat function, we have replaced it to LISTAGG in custom reports. in Stage environment we are able to execute these reports without any problem , Test environment is upgraded to 9.42 version and we have deployed custom reports from Stage to Test , Reports in which we have used LISTAGG function are giving error when report is executed..
ORA-01489: result of string concatenation is too long: 20: ORA-01489: result of string concatenation is too long
We have used other then LISTAGG fuction like
1 select rtrim(xmlcast(xmlagg(xmlelement(e,PP.CONTAINER_NAME,'')) as clob),',') and
2 SELECT RTRIM(XMLAGG(XMLELEMENT(E,PP.CONTAINER_NAME,',').EXTRACT('//text()') ORDER BY PP.CONTAINER_NAME).GetClobVal(),',')
We are able to execute report without any problem in Stage environment. where as it is giving error in Test Environment.
Can you please let me know any solution .
Re: Custom Report giving Error
It could the amount of data in your Stage and Test environment that is causing the issue.
Can you try this instead:
SELECT RTRIM(XMLAGG(XMLELEMENT(E, B.p_id|| '#@#')).EXTRACT('//text()'), '#@#') Program_ID , RTRIM(XMLAGG(XMLELEMENT(E, B.c_NAme|| '#@#')).EXTRACT('//text()'), '#@#') Program_Name
FROM (SELECT PROGRAM_ID P_ID, CONTAINER_NAME C_NAME FROM pgm_programs ) b;