Oracle Communities

Oracle Analytics and AI

Child Item

LISTAGG function usage in BI Report causing 'IO Connection Closed' error

Hi Experts,

We recently had an issue in our PROD instance that one of the report (which was working earlier) is causing issue saying 'IO Connection Closed'. We figured that LISTAGG function is causing the issue and the same report is working in other instances.

Since we don't want to change the logic, we need to fix the problem in PROD without touching the code.

So need suggestions/options/solutions on this issue. Kindly comment if anybody awares about it.

Thanks,

Keerthana.

Find more posts tagged with

BI Publisher

Accepted answers

KeerthanaBaskaran

Hi All,

We have identified that this is due to character limitation issue in the Listagg. Also we observed the same report is working for sysdate and it is not working for specific date ranges.

We have handled Overflow truncate in LISTAGG to fix our issue. As expected, the report is working.

All comments

Sumanth V -Oracle

@KeerthanaBaskaran - Please confirm that the PROD instance matches the configuration and patch level of other environments. Also, try comparing the physical SQL generated in working and non working environment and see if there are any differences. Execute them directly on corresponding DB's and validate the performance. These steps should help us identify the bottleneck first.

Rich Merkel

Is the production instance newer/have data that is not in lower PODs? What is length of all data your are trying listagg together? Just wondering if data in prod exists that is not in lower pod.

In one case we used listagg to compile invoices related to a payment. When using listagg, the number of characters exceeded 1000 (I believe that is listagg limit).

What we had to do was use xml aggregate instead of listagg. I believe we also faced I/O error.

KeerthanaBaskaran

Hi All,

We have identified that this is due to character limitation issue in the Listagg. Also we observed the same report is working for sysdate and it is not working for specific date ranges.

We have handled Overflow truncate in LISTAGG to fix our issue. As expected, the report is working.

Bhaskar Konar

https://community.oracle.com/products/oracleanalytics/discussion/comment/62969#Comment_62969

Very helpful information.

Thanks for sharing @KeerthanaBaskaran!