Jasper ORA-01795: maximum number of expressions in a list is 1000
Article ID: 142235
Updated On:
Products
CA Identity Manager
CA Identity Governance
CA Identity Portal
CA Identity Suite
Issue/Introduction
Some reports contain a large amount of reviewers.
When selecting all the reviewers in the selection screen of Jasper reports, and then attempting to run it, the report fails with the error:
ORA-01795: maximum number of expressions in a list is 1000
Environment
Identity governance 14.x
Oracle Database
Cause
This is an inherit limitation of Oracle database.
Resolution
This is an inherit limitation in the Oracle IN clause. It does not accept more than 1000 entries.
I suggest the following workaround (there is no other way to resolve it other than break the report to chunks or select several reviewers at a time):
1. If you want to select specific reviewers, you can select the specific users (should be below 1000) and proceed.
2. If you want to retrieve all the reviewers irrespective of no. of reviewers (even more than 1000), follow the below steps.
a. Currently the reviewers field is mandatory to select. So, make the field as not mandatory from Jasper.
1. Login Jasper as superuser.
2. Go to View-> Repository, Under IG Organization--> IG--> Inputs Controls --> Common. Select the one with key $R{pValuesforReviewerUserName_KEY} and click "Edit" at the top of the screen.
3. In the new page, uncheck the "Mandatory" checkbox and save.
b. Go to IG UI and Open the Certification report.
c. If you want to retrieve all the Reviewers data, don't select any reviewer in the Reviewer field. By default, Jasper assumes that all selected.
With this workaround, you can get all reviewers data irrespective of no. of reviewers. However, it does not support if we select more than 1000 in the select box.
I suggest the following workaround (there is no other way to resolve it other than break the report to chunks or select several reviewers at a time):
1. If you want to select specific reviewers, you can select the specific users (should be below 1000) and proceed.
2. If you want to retrieve all the reviewers irrespective of no. of reviewers (even more than 1000), follow the below steps.
a. Currently the reviewers field is mandatory to select. So, make the field as not mandatory from Jasper.
1. Login Jasper as superuser.
2. Go to View-> Repository, Under IG Organization--> IG--> Inputs Controls --> Common. Select the one with key $R{pValuesforReviewerUserName_KEY} and click "Edit" at the top of the screen.
3. In the new page, uncheck the "Mandatory" checkbox and save.
b. Go to IG UI and Open the Certification report.
c. If you want to retrieve all the Reviewers data, don't select any reviewer in the Reviewer field. By default, Jasper assumes that all selected.
With this workaround, you can get all reviewers data irrespective of no. of reviewers. However, it does not support if we select more than 1000 in the select box.
Feedback
Yes
No
Powered by
