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

Products

• CA eHealth

Issue/Introduction

Description:

When we try to access policy tab in OCE, it is showing JAVA.SQL.SQLEXCEPTION error and policy window is blank without listing any policies.

Solution:

When we try to create policies more than 1k which is actual limit of oracle, then it will throw java.sql.SQLException: ORA-01795 error. Then we will not able to access policies windows from OCE . In order to fix this issue, we need to bring policies count to below 1k.

We can delete policies from SQL prompt, when there is no access to GUI.

-> In order to list out policies, type in the below command in CLI.

>nhdiscoverpolicy -list

Now we can delete the policies by running the below commands in SQL prompt.

sql>delete from nh_discover_policy_params where policy_id = (select policy_id from nh_discover_policy where name = '<name>');
sql>delete from nh_discover_policy where name = '<name>';
sql>commit;
sql>exit

<Please see attached file for image>

Environment

Release: LHDEVC99000-6.3-eHealth-Live Health Upgrade Elements
Component: