Loading...

ORA-01830 - National Language Support between Germany and Switzerland

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:

PARAMETER SESSION DATABASE INSTANCE
NLS_COMP BINARY BINARY BINARY
NLS_SORT GERMAN BINARY NULL
NLS_CALENDAR GREGORIAN GREGORIAN NULL
NLS_CURRENCY SFr. $ NULL
NLS_LANGUAGE GERMAN AMERICAN AMERICAN
NLS_TERRITORY SWITZERLAND AMERICA AMERICA
NLS_DATE_FORMAT DD.MM.RR DD-MON-RR NULL
NLS_TIME_FORMAT HH24:MI:SSXFF HH.MI.SSXFF AM NULL
NLS_CHARACTERSET NULL AL32UTF8 NULL
NLS_ISO_CURRENCY SWITZERLAND AMERICA NULL
NLS_DATE_LANGUAGE GERMAN AMERICAN NULL
NLS_DUAL_CURRENCY SF $ NULL
NLS_RDBMS_VERSION NULL 12.1.0.2.0 NULL
NLS_TIME_TZ_FORMAT HH24:MI:SSXFF TZR HH.MI.SSXFF AM TZR NULL
NLS_NCHAR_CONV_EXCP FALSE FALSE FALSE
NLS_LENGTH_SEMANTICS CHAR BYTE CHAR
NLS_TIMESTAMP_FORMAT DD.MM.RR HH24:MI:SSXFF DD-MON-RR HH.MI.SSXFF AM NULL
NLS_NCHAR_CHARACTERSET NULL AL16UTF16 NULL
NLS_NUMERIC_CHARACTERS .' ., NULL
NLS_TIMESTAMP_TZ_FORMAT DD.MM.RR HH24:MI:SSXFF TZR DD-MON-RR HH.MI.SSXFF AM TZR NULL

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

ALTER SESSION SET NLS_TERRITORY='GERMANY';