My Oracle Support Banner

Error "ORA-01795: maximum number of expressions in a list is 1000" when addition more string values to the AME conditions in the Setup (Doc ID 2878379.1)

Last updated on JUNE 22, 2022

Applies to:

Oracle Approvals Management - Version 12.1.3 and later
Information in this document applies to any platform.

Symptoms

 
In : 12.1.3 version, AME Setup

When attempting to add more than 1000 strings values to the AME conditions, the following error occurs.

ERROR
-----------------------

Java Exception Details.

oracle.apps.fnd.framework.OAException: oracle.jbo.SQLStmtException: JBO-27122: SQL error during statement preparation. Statement:
SELECT * FROM (select 1 from dual) QRSLT WHERE (exists (select c.condition_id from ame_conditions c ,ame_string_values asv where c.attribute_id = :1 and c.condition_id = asv.condition_id and c.condition_type = :2 and sysdate between c.start_date and nvl(c.end_date ,sysdate) and sysdate between asv.start_date and nvl(asv.end_date ,sysdate) and (select count (*) from ame_string_values sv where sv.condition_id= asv.condition_id and sysdate between sv.start_date and nvl(sv.end_date ,sysdate) ) = :3 and asv.string_value in ( :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15, :16, :17, :18, :19, :20, :21, :22, :23, :24, :25, :26, :27, :28, :29, :30, :31, :32, :33, :34, :35, :36, :37, :38, :39, :40, :41, :42, :43, :44, :45, :46, :47, :48, :49, :50, :51, :52, :53, :54, :55, :56, :57, :58, :59, :60, :61, :62, :63, :64, :65, :66, :67, :68, :69, :70, :71, :72, :73, :74, :75, :76, :77, :78, :79, :80, :81, :82, :83, :84, :85, :86 ) group by c.condition_id having count(*) = :1005 ))
at oracle.apps.fnd.framework.OAException.wrapperException(OAException.java:912)
at oracle.apps.fnd.framework.OAException.wrapperException(OAException.java:886)
at oracle.apps.fnd.framework.OAException.wrapperInvocationTargetException(OAException.java:1009)
at oracle.apps.fnd.framework.server.OAUtility.invokeMethod(OAUtility.java:211)

## Detail 0 ##

java.sql.SQLSyntaxErrorException: ORA-01795: maximum number of expressions in a list is 1000

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:439)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:395)

STEPS
-----------------------

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

1. Go to Responsibility - Approvals Management Business Analyst
2. Click on Setup icon for Transaction Type - Purchase Requisition Approval
3. Go to Setup Tab > Conditions > click Update (yellow pencil icon) on the condition name.

Example = 'XXX_PROJECT_CODE'

4. It will be the list of String Values. It can be added a new string value when user need add the new values.  Note. User cannot add value anymore when it reaches 1000 items. or Enter the String value more than 1000 values

 

 Example :

String_Value in ( :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15, :16, :17, :18, :19, :20, :21, :22, :23, :24, :25, :26, :27, :28, :29, :30, :31, :32, :33, :34, :35, :36, :37, :38, :39, :40, :41, :42, :43, :44, :45, :46, :47, :48, :49, :50, :51, :52, :53, :54, :55, :56, :57, :58, :59, :60, :61, :62, :63, :64, :65, :66, :67, :68, :69, :70, :71, :72, :73, :74, :75, :76, :77, :78, :79, :80, :81, :82, :83, :84, :85, :86 ) group by c.condition_id having count(*) = :1005 ))

Note: Above string value says only 86 but if user is try to create more than 1000 then getting above mentioned error.

 

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Cause
Solution


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