ECC ICX Dataload is Failing With Error LISTTAG STRING CONCATENATION TOO LONG (Doc ID 3014882.1)

Last updated on APRIL 11, 2024

Applies to:

Symptoms

 The data load on data set icx-freq-items is failing:

ECC dataload is failing with error listtag string concatenation too long

SQL Query
SELECT * FROM ( SELECT /*+ leading(a.freqs) no_push_pred(a.ecc_items)*/ ecc_spec_id, LISTAGG(PREPARER_ID, '|')
WITHIN GROUP(ORDER BY PREPARER_ID) preparer_id, LISTAGG(BUSINESS_GROUP_ID, '|') WITHIN GROUP(ORDER BY BUSINESS_GROUP_ID) business_group_id, org_id, description, display_price, thumbnail_image, language, Sum(reqs_count) frequency, hide_addtocart, item_rating from icx_cat_ecc_freq_items_v a WHERE LANGUAGE in ( 'US') GROUP BY ecc_spec_id, org_id, description, display_price, thumbnail_image, language, hide_addtocart, item_rating ) PIVOT( MAX(description) AS DESCRIPTION, MAX(display_price) AS DISPLAY_PRICE, COUNT(1) AS IS_TRANSLATION_AVAILABLE FOR LANGUAGE IN ('US' "US"))

Status Message
There is an error while trying to ingest data for this load rule ICX_ECC_UTIL_PVT.get_ecc_data_load_info with dataset icx-freq-items for job 3,683,074 ORA-01489: result of string concatenation is too long oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:509) oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:461) oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1104) oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:550) .

1) Patches 35910985 and 35625136 and Patch 35910929 are applied .
2) please run the script called patchEccFiles.pl  for the following Applications:   icx_ecc.zip

The issue can be reproduced at will with the following steps:

1. Log in to Oracle Applications.
2. Navigate to a Purchasing responsibility > Requests > Run
3. Submit the iProcurement ECC Data Load concurrent program with Load Type =Full.

Changes

 Migrated from ECC version 8 to ECC version 11.

Cause