After running the Full Load Data Warehouse job, you are getting an error containing the following:
[CA Clarity][Oracle JDBC Driver][Oracle]ORA-01843: not a valid month
Release : Any
Component : Clarity Data Warehouse
nls_date_format Oracle database parameter is not configured as per the requirements.
Compare your database parameters with the required settings for your release, with special focus on the nls_date_format parameter.
If you have granted V_$PARAMETER rights to Clarity and DWH schemas, you can check the current parameters if you go to Administration > Security and Diagnostics > Health Report, and check both Database and Data Warehouse tabs and correct the Database Parameters settings that are marked with a red diamond.
If you have not granted V_$PARAMETER rights to those schemas, check with your Database Administrator.
If the issue is 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