PMO Accelerator install failing ORA-01858: a non-numeric character was found where a numeric was expected

Products

• Clarity PPM On Premise

Issue/Introduction

On a new vanilla install, 

 when I try to install PMO accelerator, I’m getting the below error:

 

 

Error Applying XOG: Failure occurred while applying projects/csk_infrastructure.xml

 Check /ngs/app/hppmt/clarity/logs/content/xog/csk/projects/csk_infrastructure_out.xml for errors

 

Caused by: java.sql.SQLException: [CA Clarity][Oracle JDBC Driver][Oracle]ORA-01858: a non-numeric character was found where a numeric was expected

ORA-06512: at "PPM_OWNER.CMN_DATE_TIME_DIFF_FCT", line 16

 

Can you please provide some guidance on how I can pass this stage.

Environment

Release : 15.9.1, 15.9.3

Component : PMO Content Add-in

Resolution

Clarity requires NLS_DATE_FORMAT be set to 

nls_date_format = 'YYYY-MM-DD HH24:MI:SS'

One way to do this is to set it at a system level:

alter system set nls_date_format = 'YYYY-MM-DD HH24:MI:SS' scope=spfile;

Another would be to create a login trigger for that user:

-- This script is provided for illustration purposes only.  Please work with your DBA on the appropriate solution

CREATE OR REPLACE TRIGGER set_nls_date_format AFTER LOGON ON DATABASE
DECLARE
username VARCHAR2(30);
cmmd VARCHAR2(64);
BEGIN
cmmd:='ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'';
username:=SYS_CONTEXT('USERENV','SESSION_USER');
IF username LIKE 'PPM_OWNER%' then
EXECUTE IMMEDIATE cmmd;
EXECUTE IMMEDIATE cmmd2;
END IF;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
/

However, most of our customers set this at a system level.

 

select sysdate from dual;

Additional Information

Required Oracle Parameters for Clarity