JR42757: CMVC 221928 - LIMIT DATA RETRIEVAL QUERIES TO 1000 ROWS.

WebSphere Commerce Version 7.0.0.7 Fix Pack

APAR status

• Closed as program error.

Error description

• When trying to retrieve a catgroup description, the following
  error is seen:
  
  
   CNTR0019E: EJBthrew an unexpected (non-declared) exception
  during invocation ofmethod "findByCatalogGroupIdsAndLanguageId".
  Exception data:com.ibm.ejs.persistence.EJSPersistenceException:
  find failed:;  nestedexception is:
  java.sql.SQLSyntaxErrorException: ORA-01795: maximum number of
  expressions in a list is 1000...
  at
  com.ibm.commerce.catalog.objsrc.CatalogGroupDescriptionCache.fin
  dByCatalogGroupIdsAndLanguageId(CatalogGroupDescriptionCache.jav
  a:434)
  at
  com.ibm.commerce.catalog.beans.CatalogDataBean.getTopCategories(
  CatalogDataBean.java:538)
  at
  com.ibm.commerce.catalog.facade.server.commands.ComposeCatalogGr
  oupDetailsTaskCmdImpl.isTopCategory(ComposeCatalogGroupDetailsTa
  skCmdImpl.java:400)
  at
  com.ibm.commerce.catalog.facade.server.commands.ComposeCatalogGr
  oupDetailsTaskCmdImpl.execute(ComposeCatalogGroupDetailsTaskCmdI
  mpl.java:170)
  at
  com.ibm.commerce.catalog.facade.server.commands.AbstractFetchCat
  alogGroupSOICmdImpl.execute(AbstractFetchCatalogGroupSOICmdImpl.
  java:234)
  ...
  
  This happens when using an oracle database, when trying to
  retrieve over 1000 catgroup descriptions at once. This is due to
  an Oracle limitation where only 1000 entries can be added to an
  IN clause.
  

Local fix

Problem summary

• USERS AFFECTED:
  All users on WebSphere Commerce V7 who use Oracle
  
  PROBLEM ABSTRACT:
  When using oracle, if there are over 1000 top categories in the
  catalog,  see the error ' ORA-01795: maximum number of
  expressions in a list is 1000' when trying to retrieve a
  catgroup description
  
  BUSINESS IMPACT:
  Catalog Navigation could fail if the catalog has over 1000 top
  catgroups
  
  RECOMMENDATION:
  

Problem conclusion

• In APAR JR42757, the data cache was changed to use multiple
  executions of the finder method when more than 1000 category ids
  are in the query.
  
  Similar situations were also fixed in the same way for
  CatalogEntry, CatalogEntryDescription, CatalogDescription,
  CatalogEntryShipping, and OfferPrice finder methods.
  
  -------------------------------------------------------------
  The latest available maintenance information can be obtained
  from the Recommended Fixes for WebSphere Commerce technote:
  http://www.ibm.com/support/docview.wss?rs=3046&uid=swg21261296
  

Temporary fix

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

Fix information

• Fixed component name

  WC BUS EDITION

• Fixed component ID

  5724I3800

Applicable component levels

• R700 PSY

     UP