Question: Running Image with different Timezone and Locale

[arolfes]

Hello @gvenzl ,

I wanna use your image via testcontainer for our TASKANA Tests.

Therefore I want to set the locale and timezone for the database. How to do that?

Current solution does not have any effect:

            new OracleContainer(
                        DockerImageName.parse("gvenzl/oracle-xe:21-slim-faststart")
                ).withDatabaseName("taskana")
                .withUsername(ORACLE_SCHEMA)
                .withPassword("testPassword")
                .withCommand(
                    "/bin/sh",
                    "-c",
                    "localedef -i de_DE -c -f UTF-8 -A /usr/share/locale/locale.alias de_DE.UTF-8 "
                    + "&& export LANG=de_DE.UTF-8 "
                    + "&& export TZ=CET "
                    + "&& ./container-entrypoint.sh")
        );

Do you have an idea?

[gvenzl]

Hi @arolfes,

Thanks a lot for considering using these images!

The database doesn't rely on the OS locale but rather uses its own. These settings can be specified for each individual connection so that, for example, the database can be in Austria, but people in England and France can connect to it using the locale that they are used to. In short, this means that these settings need to be set on the client rather than on the database side.

There is a client-side environment variable NLS_LANG that when set on the client transmits the correct locale information to the database. If you set it to NLS_LANG=GERMAN_GERMANY.AL32UTF8 you will have the same as on Linux LANG=de_DE.UTF-8, for example:

bash-4.4$ export NLS_LANG=GERMAN_GERMANY.AL32UTF8
bash-4.4$ sqlplus system/LetsTest1@docker-con-db/XEPDB1

SQL*Plus: Release 21.0.0.0.0 - Production on Mi Nov 30 06:47:09 2022
Version 21.3.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.

Verbunden mit:
Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0

SQL> set feedback on;
SQL> select sysdate from dual;

SYSDATE
--------
30.11.22

1 Zeile wurde ausgewählt.

SQL> exit
Verbindung zu Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0 beendet

Regarding the timezone, there are a couple of places where those come to play. If you just want to change the values of SYSTIMESTAMP and SYSDATE, you are on the right track. You will have to change the timezone of the underlying container as these values are retrieved from the system, i.e. OS, hence the SYS....

[oracle@83ce4b71d52f ~]$ date
Sat Dec  3 18:02:51 UTC 2022
[oracle@83ce4b71d52f ~]$ export TZ="Europe/Vienna"
[oracle@83ce4b71d52f ~]$ date
Sat Dec  3 19:03:00 CET 2022
[oracle@83ce4b71d52f ~]$ sqlplus / as sysdba

SQL*Plus: Release 21.0.0.0.0 - Production on Sat Dec 3 19:03:09 2022
Version 21.3.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.

Connected to:
Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0

SQL> alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';

Session altered.

SQL> select sysdate from dual;

SYSDATE
-------------------
2022-12-03 19:03:26

SQL> select systimestamp from dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
03-DEC-22 07.03.34.702579 PM +01:00

Is that what you are looking for?

[arolfes]

Thank you that was what I'm looking for

so this code works:

OracleContainer("gvenzl/oracle-xe:18-slim-faststart")
                .withDatabaseName("taskana")
                .withUsername("TEST_USER")
                .withPassword("testPassword")
                .withEnv("TZ", "Europe/Berlin")
                .withEnv("NLS_LANG", "GERMAN_GERMANY.AL32UTF8")