Resolving ORA-01795 Error in Identity Governance Jasper Reports

Products

• CA Identity Governance
• CA Identity Suite

Issue/Introduction

Jasper reports may fail when selecting a large volume of reviewers, specifically exceeding the Oracle database limit of 1,000 expressions in an IN clause. This article provides workarounds to bypass the ORA-01795 error.

Environment

Identity Governance 14.5

Cause

The error ORA-01795: maximum number of expressions in a list is 1000 is caused by an inherent limitation in the Oracle SQL IN clause, which prevents the database from processing more than 1,000 entries in a single query list.

Resolution

To bypass this limitation, apply one of the following methods:

Method 1: Select Fewer Reviewers If you require data for specific users, select fewer than 1,000 reviewers in the report selection screen and execute the report.

Method 2: Remove Mandatory Selection Constraint If you require data for all reviewers (exceeding 1,000 entries), you must modify the input control in Jasper to make the field non-mandatory:

1. Log in to Jasper as a superuser.
2. Navigate to View > Repository.
3. Go to IG Organization > IG > Inputs Controls > Common.
4. Locate the control with the key $R{pValuesforReviewerUserName_KEY} and click Edit.
5. Uncheck the Mandatory checkbox and save the changes.
6. Open the Certification report in the Identity Governance UI.
7. Execute the report without selecting any reviewers; by default, Jasper will assume all reviewers are selected.

Note: This workaround allows you to retrieve all data but does not support selecting a specific subset of users if that subset exceeds 1,000 entries.

Additional Information

To speak with a customer representative or a Support Engineer see Contact Support. Scroll to the bottom of the page and click on your respective region.