MonthAversary Activity - ORA-01795:Maximum Number Of Expressions in a Lis tis 1000” (Doc ID 3056852.1)

Last updated on NOVEMBER 08, 2024

Applies to:

Symptoms

On : 11.0.2.33 version, Client Activity

The following message is reported when attempting to process the MonthAversary Activity:


ERROR
-----------------------
“Error Code: 1795 Internal Exception: java.sql.SQLSyntaxErrorException: ORA-01795: maximum number of expressions in a list is 1000”
SELECT depositValuationEffectGuid, cashValue, units FROM ( SELECT AsDepositValue.depositValuationEffectGuid, InitialCashValue.floatValue AS cashValue, InitialUnits.floatValue AS units, ROW_NUMBER() OVER( PARTITION BY AsDepositValue.depositValuationEffectGUID ORDER BY AsActivity.activityGmt DESC ) rowNumber FROM AsActivity JOIN AsDepositValue ON AsActivity.activityGuid = AsDepositValue.activityGuid AND AsDepositValue.beginEndFlag = 'E' JOIN AsFund ON AsDepositValue.fundGuid = AsFund.fundGuid LEFT OUTER JOIN AsDepositValueField InitialCashValue ON InitialCashValue.depositValueGuid = AsDepositValue.depositValueGuid AND InitialCashValue.FieldName = 'InitialCashValue' LEFT OUTER JOIN AsDepositValueField InitialUnits ON InitialUnits.depositValueGuid = AsDepositValue.depositValueGuid AND InitialUnits.FieldName = 'InitialUnits' WHERE AsActivity.policyGuid = ? AND AsActivity.TypeCode IN ('01','04') AND AsActivity.StatusCode IN ('01','13','14') AND AsDepositValue.depositValuationEffectGuid IN


STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Login to OIPA.
2. Locate the Policy.
3. Process the Activities through Process All button.
4. On processing Monthaversary through batch, it will errored out with a blank white screen.
5. The Current count is 1052 and maximum limit is 1000 that is causing the issue.

Changes

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.