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


Updated On:


Clarity PPM On Premise Clarity PPM SaaS


  1. The portfolio synch job is failing with the below error when we create a large power filter expression
  2. We can sync about 2400 programs fine, but if we sync around 2500+ it starts to fail.
  3. 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, 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 = 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, 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


  1. This is due to a limitation in the amount of characters allowed in the power filter.
  2. 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



Below are best practice recommendations to add a large number of projects into a portfolio:
  1. 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:
      1. Create a custom attribute on the project object
      2. Assign all the projects that should be part of the same portfolio the same value for that custom attribute
      3. Then create a power filter in the portfolio based on that custom attribute 
  2. Manually add the projects under the Individual Investments section of the Contents Editor tab in the Portfolio

Additional Information

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