Clarity with Oracle throws ORA-01861: literal does not match format string

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

Clarity on Oracle throws ORA-01861: literal does not match format string in app-ca.log on any actions in UI that involve dates (such as filters, power filters etc.)

System error in UI will happen when filtering on portlets including dates, or running jobs including dates.

For example, if you filter for a timesheet in a prior or current time period, you see "System Error. Contact system administrator."

When looking in the app-ca log or bg-ca.log for jobs, error message is thrown:

ORA-01861: literal does not match format string

Environment

Release: Any Supported Release

Cause

NLS_DATE_FORMAT should be set correctly on the database schema level.

Resolution

• Check the CSA HealthReport and see if you see a correct date format and other settings.
• If not you may have to correct it. Sometimes it displays correctly even when not set properly. 
• Check on the database side with query:
  • Select parameter, value
    from nls_instance_parameters
    where parameter in ('NLS_LANGUAGE', 'NLS_TERRITORY', 'NLS_CHARACTERSET', 'NLS_RDBMS_VERSION', 
      'NLS_DATE_FORMAT', 'NLS_SORT', 'NLS_COMP')
• Make sure the OS locale is set to US and database OS also set to US.
• Make sure the other NLS parameters are set accordingly as per Installation guide, especially NLS_TERRITORY.

If the issue is only with NLS_DATE_FORMAT, to resolve this, try one of the following solutions:

Basic reset of NLS_DATE_FORMAT:
1. Run the command:

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

2. Restart the database (the change requires a restart)

Advanced reset of NLS_DATE_FORMAT

0. create pfile from spfile;
1. Edit the pfile ora file and change this to
*.nls_date_format='YYYY-MM-DD HH24:MI:SS'

2. Then recreate the SPFILE.
- Edit the pfile
- Shutdown the instance
- Create spfile from pfile
- Startup the database

Additional Information

For additional comments here: Communities post