more shipto accounts issue for a b2b customer(more than 900)

[itzsravan]

if a customer is having more than 900 shipto accounts, we have been facing an issue while opening the order history and order details page. The error is occurring while populating the product in the order.

in the file DefaultConfiguratorSettingsResolutionStrategy.java, line number 172

final Collection<CategoryModel> supercategories = product.getSupercategories();

We are getting issue while retrieving the Supercategories of the product.

Error query : SELECT {item:pk} FROM {CategoryProductRelation* AS rel JOIN Category AS item ON {rel:source}={item:pk} } WHERE {rel:qualifier} = 'CategoryProductRelation' AND {rel:target} = '8796108193793' AND {rel:language} IS NULL ORDER BY {rel:pk} ASC ( item='8796108193793',quali='CategoryProductRelation',lang='null')

LOGs:

....select '8846286049101' from dual UNION select '8810839363405' from dual UNION select '8837133651789' from dual UNION select '8842446688077' from dual)SELECT item_t1.PK FROM cat2prodrel item_t0 JOIN categories item_t1 ON item_t0.SourcePK = item_t1.PK WHERE ( item_t0.Qualifier = ? AND item_t0.TargetPK = ? AND item_t0.languagepk IS NULL) AND (item_t1.TypePkString IN (?,?,?,?,?) AND ((EXISTS (SELECT item_t2.PK FROM cat2princrel item_t2 WHERE ( item_t2.SourcePK = item_t1.PK AND item_t2.TargetPK IN ( SELECT (id) FROM with_tbl_session_user_allGroups1 )) AND (item_t2.TypePkString=? ))) AND ( item_t1.p_catalogversion IN (?,?,?))) ) order by item_t0.PK ASC', values = [CategoryProductRelation, 8797141630977, 8796107800658, 8796147253330, 8796129558610, 8796129525842, 8796094365778, 8796107964498, PropertyValue:8796093219417, PropertyValue:8796093186649, PropertyValue:8796093284953] at de.hybris.platform.jalo.flexiblesearch.internal.FlexibleSearchExecutor.execute(FlexibleSearchExecutor.java:242) ~[coreserver.jar:?] at de.hybris.platform.jalo.flexiblesearch.FlexibleSearch.executeSearch(FlexibleSearch.java:1884) ~[coreserver.jar:?] at de.hybris.platform.jalo.flexiblesearch.FlexibleSearchCacheUnit.compute(FlexibleSearchCacheUnit.java:51) ~[coreserver.jar:?] at de.hybris.platform.cache.AbstractCacheUnit.privateGetNoLock(AbstractCacheUnit.java:305) ~[coreserver.jar:?] at de.hybris.platform.cache.AbstractCacheUnit.privateGet(AbstractCacheUnit.java:278) ~[coreserver.jar:?] at de.hybris.platform.cache.AbstractCacheUnit.get(AbstractCacheUnit.java:180) ~[coreserver.jar:?] at de.hybris.platform.jalo.flexiblesearch.FlexibleSearchCacheUnit.myGet(FlexibleSearchCacheUnit.java:90) ~[coreserver.jar:?] at de.hybris.platform.jalo.flexiblesearch.FlexibleSearch.search(FlexibleSearch.java:1455) ~[coreserver.jar:?] at de.hybris.platform.jalo.flexiblesearch.FlexibleSearch.search(FlexibleSearch.java:1382) ~[coreserver.jar:?] at de.hybris.platform.jalo.link.LinkManager.getLinkedItems(LinkManager.java:520) ~[coreserver.jar:?] ... 177 more Caused by: java.sql.SQLSyntaxErrorException: ORA-00972: identifier is too long

Thankful to any help 🙂

[itzsravan · Accepted solution]

the issue is with the temporary table name being generated while fetching the data. Oracle will not accept more than 30 characters if the version is below 12.1,

there is no other way to fix this by code customization. We have to upgrade the oracle to 12.2 or more.