Hi Ludo,
Yes, for your immediate errors, the NLS_DATE_FORMAT is certainly at issue here. The 'session' responses are OK if they do not match the exact requirements, because some of these are altered by Clarity on the fly (at the temporary session level) in order to serve results back to users according to their settings in Clarity for language and locale.
However, there are many core queries and interactions with the database that happen at a level before the individual user's preferences get applied, and in those instances it will struggle to handle settings that may change from one server/configuration to the next.
You can check the init.ora to see how the setting is there, but also in my post there was a second query to check the contents of the v$parameter system view. If the value needs changing there too, the configuration in the init.ora may not be sufficient and it has to be resolved through Oracle's configuration management or a DBA query such as the following:
alter system set nls_date_format = 'yyyy-mm-dd hh24:mi:ss' scope=spfile;
Oracle needs restarting after any init.ora or spfile scope changes for that to take effect. scope=memory or scope=both can be used for some parameters so that they take immediate effect without restarting, but if this isn't possible Oracle will raise an error when running the query and then there will be no alternative except to use scope=spfile and to restart Oracle.
With the settings you have shown I would expect you to encounter some other problems further down the line and would recommend some other changes too.
First is NLS_SORT which should be set to NLS_SORT=BINARY.
Again, when Clarity is performing a sort that needs to be language-sensitive (French) it will apply that to the user's session, but there are other times when a natural non-language specific sort is the correct approach, and in those cases Clarity will be expecting results to be provided in BINARY order. This setting can also affect the performance of database indexes when used in sorting, since your indexes will not be stored with NLS_SORT=FRENCH considerations and yet a number of queries will run and be subjected to this rule.
Lastly, there are other areas in the application where you can encounter number format errors and issues due to the NLS_TERRITORY value. An exact place I have seen this can happen is during the installation of Clarity, but there can be other places too.
The issue can be demonstrated by the following script that temporarily changes the value and shows the impact it will have on queries that it tries to run:
-- this is just a temporary change for the logged
-- in connection and session, it will not persist
-- after logout, it is just to demonstrate
alter session set nls_territory='america';
create table n(f number);
insert into n(f) values ('8.0');
-- or nls_territory='france' or any other
-- where the number format is expected
-- to be different for decimal identification
alter session set nls_territory='sweden';
-- this will now cause ORA-01722: invalid number
-- even though it worked before with the other nls_territory
insert into n(f) values ('8.0');
-- this is the format it expects now
insert into n(f) values ('8,0');
rollback;
-- only run the following statement when you're
-- comfortable to do so in case table 'n' already
-- existed for another purpose in the DB before
-- this script ran
-- drop table n;
Even if there are no problems right now and you decide not to change this, when it does occur this will be the reason why.
Thanks,
-Nick
P.s. These considerations above aren't GEL/XOG/WSDL specific and can occur elsewhere in the system, but may take time to surface.