Export to CSV with MVL lookups is incomplete and throws ORA-01795: maximum number of expressions in a list is 1000

Products

• Clarity PPM On Premise
• Clarity PPM SaaS

Issue/Introduction

You have added a few MVL lookups to Project Grid and would like to be able to export them together will the entire list, export CSV is incomplete and error is thrown  throws ORA-01795: maximum number of expressions in a list is 1000

 

STEPS TO REPRODUCE:

1. Connect as admin user on a system with 1000+ instances of Project/ Investments / Custom Objects 
2. Create a custom Static lookup with 5-10 entries and an attribute for it
3. Open MUX Project Grid with view that includes this attributes and shows over 1000 rows.
4. Note the amount of records is 1000+
5. Click on Export to CSV button

Expected Results: All the rows to export successfully in the CSV

Actual Results:

• The CSV Export is successful, upon opening the CSV we can see it only has 567 rows. In bg-ca.log we can see this is due to hitting an Oracle limitation:
• Error message: ORA-01795: maximum number of expressions in a list is 1000. 
• All the project ID entries seem to be listed in an IN clause.

 Note: this also happens with dynamic lookups, as long as the lookup is MVL

Instances in the export will be partially or entirely missing - it does not retrieve all records

Environment

Clarity 16.2.1, 16.2.2, 16.2.3, 16.3.0

Cause

DE171775

Resolution

• Fixed in Clarity 16.3.1

Workaround for earlier releases:

1. Run the query: 
   UPDATE CMN_GOV_LIMITS SET CURRENT_LIMIT=1000, DEF_LIMIT=1000 WHERE CODE='ASYNC_E2CSV_BATCH_SIZE';
2. Restart all services