Error ORA-00933 on creating or updating audited attributes on Oracle (on 16.2.3.5 and 16.3.3(base))

Products

• Clarity PPM On Premise
• Clarity PPM SaaS

Issue/Introduction

After the change made to fix DE168694, and applying the feature toggle, seeing the error when creating a project with audited attributes, or updating audited attributes via XOG, the issue is only on Oracle On Premise.

STEPS TO REPRODUCE:

1. On a system with Clarity 16.2.3.5 or 16.3.3(base), set up a few attributes to be audited for attribute update on Project object
2. Make sure you apply the feature toggle and bootstrap command for feature IMPERSONATEDBY_SESSION_CONTEXT_VARIABLE_DE168694  

• admin toggle-feature  IMPERSONATEDBY_SESSION_CONTEXT_VARIABLE_DE168694   1
• admin db db-bootstrap-odf -Dodf.triggers=true -Dodf.force=false -Dodf.restoreNonCustomizedViews=false

3. Now try creating a new project or XOG project update on some of the audited attributes

Expected Results: The updates to complete successfully

Actual Results: Error thrown in the logs:

Caused by: Error : 933, Position : 75, Sql = select sys_context('CLARITY', 'IMPERSONATEDBY') as impersonatedby FROM DUAL;, OriginalSql = select sys_context('CLARITY', 'IMPERSONATEDBY') as impersonatedby FROM DUAL;, Error Msg = ORA-00933: SQL command not properly ended

Environment

Clarity 16.2.3.5 and 16.3.3 (base) with Oracle Only

Cause

DE175197 

Resolution

Fixed in

• Clarity 16.3.3 Patch 1 (16.3.3.1)
• Clarity 16.4.0

Workaround:

1. Bring all services down
2. Edit the file $clarity\META-INF\security\pmd\securityPMD.xml

In <statementSet id="security.getImpersonationContext"
      <sql dbVendor="oracle">
       <text>
          select sys_context('CLARITY', 'IMPERSONATEDBY') as impersonatedby FROM DUAL;
        </text>
      </sql>

Remove the semicolon after DUAL

3. Save and restart the services