There were more rows than could be returned error occurs when executing LookupValue in Siebel

APPLIES TO:

Siebel CRM - Version 8.1.1.11 [IP2013] and later
Information in this document applies to any platform.
*** Checked for currency JUL-22-2016 ***

SYMPTOMS

Environment:
------------
Siebel CRM 8.1.1.11 [23030]
Microsoft Windows (32-bit) 2003

Statement of Issue:
-------------------

An external application sends a record to Siebel. An UI data map is used to convert the record. This includes the expression LookupValue("MSC_WARD",[MSC Ward]) to convert an incoming WARD code into the required value in Siebel. In Siebel there are 11400 List Of Value records where Type = MSC Ward, but there is only 1 record with the relevant Type and LIC combination. When the external application sends a record to Siebel, an error is occurring.

Error:
------
Error invoking service 'EAI Data Transformation Engine', method 'Execute' at step 'DataMap'.(SBL-BPR-00162)
--
An error occurred while attempting to look up the LOV value of '0512005' of type 'MSC_WARD'.(SBL-DAT-00590)
--
There were more rows than could be returned. Please refine your query to bring back fewer rows(SBL-DAT-00500)

Error Code:
(SBL-BPR-00162)--(SBL-DAT-00590)--(SBL-DAT-00500)

CAUSE

When LookupValue is executed, the log shows that the application is executing a select statement to retrieve all of the List Of Values records where LANG_ID = ENU and TYPE = MSC_WARD in order to load them into the LOV cache. More than 10,000 records are returned and the “There were more rows than could be returned.” error is occurring. This is expected behavior.
 

SOLUTION

The number of records that can be returned is controlled by the parameter DSMaxFetchArraySize in the ServerDataSrc named subsystem. The following information is included in [Setting the Parameter DSMaxFetchArraySize to -1 Could Cause Problems with Siebel Object Managers in Siebel 7] (Doc ID 477558.1):

“DSMaxFetchArraySize is a named subsystem parameter that controls the maximum number of records that can be returned by a business component in ForwardBackward mode. It does not restrict the number of records returned for ForwardOnly cursors. By default, the DSMaxFetchArraySize parameter has a value of 0. When this parameter is set to 0, the Object Manager initializes the parameter to 10,000. This means that a maximum of 10,000 records can be returned by a business component in ForwardBackward mode.

When records are being retrieved from a business component in ForwardBackward mode, if the number of records retrieved is greater than the value of DSMaxFetchArraySize, (10,000 by default) then the following message will be displayed:

SBL-DAT-00500: "There were more rows than could be returned. Please refine your query to bring back fewer rows."

This is the expected behavior. An example of when this message would appear is when a user scrolls through the records in a list applet and tries to access the 10,001 th record (assuming the default value of DSMaxFetchArraySize is in place).

It is possible to alter this behavior by setting the DSMaxFetchArraySize to -1 (unlimited). This means that there is no limit to the number of records returned by a business component in ForwardBackward mode. All the records of the business component are enumerated. Essentially, all the records retrieved will stay in the memory until the next business component execution or when the business component is destroyed. Setting DSMaxFetchArraySize to -1 has been found to cause large memory use and growth of the Object Manager process (siebmtshmw.exe), and could cause the Object Manager to crash from memory exhaustion.

NOTE: Setting the parameter DSMaxFetchArraySize to -1 or to a value greater than 10000 is not recommended.”

It appears that LookupValue uses ForwardBackward mode.

The customer looked into whether it’s possible to avoid the error by setting MaxCursorSize on the List Of Values (Internal) bus comp to a value greater than 11400, but it appears that the value assigned to DSMaxFetchArraySize overrides the value assigned to MaxCursorSize on the bus comp.

As mentioned in Doc 477558.1, setting DSMaxFetchArraySize to -1 (unlimited) isn’t recommended. However, an option is to set it to a higher value to prevent the error from occurring (e.g. 11500). This change would apply to the application. An option is to create a custom server data source where DSMaxFetchArraySize is set to the higher value, which could be used by the EAI OM only.

The error is occurring when the function LookupValue is executed. Another option is to retrieve the required value using a custom business service instead that queries for the required LOV record using the ForwardOnly execution mode and returns the value. The business service could be invoked from the data map. The customer avoided the error by implementing this option.

Source: Oracle Doc ID 1925807.1

See Alos:
SBL-DAT-00590 Error While Trying to Pick A Value For A Picklist Field (Doc ID 1335003.1)
DSMaxFetchArraySize = -1 Could Result IN High Memory Usage / Crash Of OM PIDs (Doc ID 477558.1)