ponguru1224 Regular Contributor.
Regular Contributor.
294 views

Custom Report giving Error

Hi,

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 .

 

Regards,

Subbu.

 

 

 

0 Likes
1 Reply
Outstanding Contributor.. Amishra Outstanding Contributor..
Outstanding Contributor..

Re: Custom Report giving Error

Hi Subbu,

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;

 

Regards,
Ajay Mishra
0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.