ORA-01830 - National Language Support between Germany and Switzerland

This post is older than a year. Consider some information might not be accurate anymore.

I have to deal with a German software vendor, which gives us some SQLs scripts for Oracle DB. Between Germany and Switzerland, there are minor differences, which led to error ORA-01830. The script contained some insert statements with timestamps.

As example

SELECT to_char(to_timestamp('29.03.16 10:14:33,260000000','DD.MM.RR HH24:MI:SSXFF')) FROM DUAL;

This will give us following exception from the Oracle 12 database

ORA-01830: date format picture ends before converting entire input string
01830. 00000 -  "date format picture ends before converting entire input string"

If I query the current timestamp

SELECT current_timestamp from DUAL;
18.05.16 09:23:41.401025000 EUROPE/BERLIN

The difference is very minor. In Switzerland the dot (.) is used instead of the comma (,). If I query with a dot, no exception will be raised.

SELECT to_char(to_timestamp('29.03.16 10:14:33.260000000','DD.MM.RR HH24:MI:SSXFF')) FROM DUAL;
29.03.16 10:14:33.260000000

To check the NLS settings

SELECT *
FROM
  (SELECT 'SESSION' SCOPE,nsp.* FROM nls_session_parameters nsp
  UNION
  SELECT 'DATABASE' SCOPE,ndp.* FROM nls_database_parameters ndp
  UNION
  SELECT 'INSTANCE' SCOPE,nip.* FROM nls_instance_parameters nip
  ) a pivot (LISTAGG(VALUE) WITHIN GROUP (
ORDER BY SCOPE) FOR SCOPE IN ('SESSION' AS "SESSION",'DATABASE' AS DATABASE,'INSTANCE' AS INSTANCE));

The result is this table:

To fix the problem, I added to the script in the beginning:

ALTER SESSION SET NLS_TERRITORY='GERMANY';