ALERT: Some images may not load properly within the Knowledge Base Article. If you see a broken image, please right-click and select 'Open image in a new tab'. We apologize for this inconvenience.
Synchronize Portfolio Investments Job fails with error ORA-00936: missing expression due to power filter limit
Article ID: 187846
Clarity PPM On PremiseClarity PPM SaaS
The portfolio synch job is failing with the below error when we create a large power filter expression
We can sync about 2400 programs fine, but if we sync around 2500+ it starts to fail.
It appears to be hitting a limitation in the amount of characters allowed in the expression.
Portfolio Sync failed: SQL error code: 936 Error message: [CA Clarity][Oracle JDBC Driver][Oracle]ORA-00936: missing expression Executed: select odf_q.* , (select name from ( select o.code code, nls.name name, nls.description description,o.LAST_UPDATED_DATE LAST_UPDATED_DATE ,l.ID LANGUAGE_ID, nls.LANGUAGE_CODE LANGUAGE_CODE from odf_objects o, cmn_captions_nls nls,CMN_LANGUAGES l where o.id = nls.pk_id and nls.language_code = ? and nls.table_name = 'ODF_OBJECTS' AND nls.LANGUAGE_CODE = l.LANGUAGE_CODE and 1=? and 1=1 and 1=1) q_odf_object_code where rownum = 1 and q_odf_object_code.code = odf_q.odf_object_code) odf_object_code_caption from ( select row_number() over ( order by odf_pk) odf_row_num, count(*) over (partition by pmd_analytical_partition_by) odf_num_rows ,odf_cols.* from ( select 'x' pmd_analytical_partition_by, inv_investments.id odf_pk , INV_INVESTMENTS.CREATED_DATE created_date , odf_ca_inv.odf_object_code odf
Note: The same issue happens if you create the power filter from the Contents Editor tab in the Add Investments section
This is due to a limitation in the amount of characters allowed in the power filter.
It’s not recommended a large power filter expression such as a couple 100s of projects in the power filter expression, but instead:
Manually add the projects in Add Investments
Or, use a different attribute in the power filter such as OBS or a custom attribute to pull in the projects.
Release : All Supported Releases
Component : CA PPM PORTFOLIO MANAGEMENT
Below are best practice recommendations to add a large number of projects into a portfolio:
Use another field in the power filter to help pull in projects that all fall into the criteria such as OBS or a custom attribute
Example using a custom attribute:
Create a custom attribute on the project object
Assign all the projects that should be part of the same portfolio the same value for that custom attribute
Then create a power filter in the portfolio based on that custom attribute
Manually add the projects under the Individual Investments section of the Contents Editor tab in the Portfolio
See also KB: 17080 - Specific portfolios throw Error: ORA-00936: missing expression due to OBS issue See KB: 141061 for tips on how to optimize use of Self Service for Clarity PPM