When using JDBC, NLS parameters are affected by the Java locale.

Execution environment

The main software versions used in writing this article are as follows. Docker and Oracle Official Docker Image are used to build the Oracle Database.

Overview

Since there are various setting methods for NLS parameters of Oracle Database, "I don't know which setting value affects how!" Occurs, but using the JDBC driver to connect to Oracle Database If so, the NLS parameters may change depending on the Java locale that is the client.

[Database Installation Guide for Linux-Setting Language and Locale Preferences for Client Connections](https://docs.oracle.com/cd/E96517_01/ladbi/setting-language-preferences-for-client- Excerpt from connections.html # GUID-78A71337-2199-4FED-B0F1-D313F769B22C):

Java applications that use Oracle JDBC to connect to the Oracle Database do not use NLS_LANG. Instead, Oracle JDBC maps the default locale of the Java VM running the application to the Oracle Database language and territory settings.

Experiment

Let's experiment. The following is a Java application that uses JDBC and outputs all the contents of V $ NLS_PARAMETERS that stores the current settings of NLS parameters.

Main1.java

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Main1 {
    public static void main(String[] args) {
        String url = "jdbc:oracle:thin:@//192.168.99.100:1521/ORCLPDB1";
        String user = "dev1";
        String password = "password";

        try (Connection c = DriverManager.getConnection(url, user, password)) {
            Statement stmt = c.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT * FROM V$NLS_PARAMETERS ORDER BY PARAMETER");
            while (rs.next()) {
                String parameter = rs.getString("PARAMETER");
                String value = rs.getString("VALUE");
                System.out.printf("%s = %s%n", parameter, value);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Let's do this by changing the Java locale. First of all, Japanese.

$ java -Duser.language=ja -Duser.country=JP -cp .:../lib/ojdbc8.jar Main1
NLS_CALENDAR = GREGORIAN
NLS_CHARACTERSET = AL32UTF8
NLS_COMP = BINARY
NLS_CURRENCY = ¥
NLS_DATE_FORMAT = RR-MM-DD
NLS_DATE_LANGUAGE = JAPANESE
NLS_DUAL_CURRENCY = \
NLS_ISO_CURRENCY = JAPAN
NLS_LANGUAGE = JAPANESE
NLS_LENGTH_SEMANTICS = BYTE
NLS_NCHAR_CHARACTERSET = AL16UTF16
NLS_NCHAR_CONV_EXCP = FALSE
NLS_NUMERIC_CHARACTERS = .,
NLS_SORT = BINARY
NLS_TERRITORY = JAPAN
NLS_TIMESTAMP_FORMAT = RR-MM-DD HH24:MI:SSXFF
NLS_TIMESTAMP_TZ_FORMAT = RR-MM-DD HH24:MI:SSXFF TZR
NLS_TIME_FORMAT = HH24:MI:SSXFF
NLS_TIME_TZ_FORMAT = HH24:MI:SSXFF TZR

Next is English.

$ java -Duser.language=en -Duser.country=US -cp .:../lib/ojdbc8.jar Main1
NLS_CALENDAR = GREGORIAN
NLS_CHARACTERSET = AL32UTF8
NLS_COMP = BINARY
NLS_CURRENCY = $
NLS_DATE_FORMAT = DD-MON-RR
NLS_DATE_LANGUAGE = AMERICAN
NLS_DUAL_CURRENCY = $
NLS_ISO_CURRENCY = AMERICA
NLS_LANGUAGE = AMERICAN
NLS_LENGTH_SEMANTICS = BYTE
NLS_NCHAR_CHARACTERSET = AL16UTF16
NLS_NCHAR_CONV_EXCP = FALSE
NLS_NUMERIC_CHARACTERS = .,
NLS_SORT = BINARY
NLS_TERRITORY = AMERICA
NLS_TIMESTAMP_FORMAT = DD-MON-RR HH.MI.SSXFF AM
NLS_TIMESTAMP_TZ_FORMAT = DD-MON-RR HH.MI.SSXFF AM TZR
NLS_TIME_FORMAT = HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT = HH.MI.SSXFF AM TZR

I found that the NLS parameters change depending on the client-side locale, even though I haven't changed any settings on the Oracle Database side.

What's scary

What's scary about this behavior is that the following things can happen:

--A module that works normally on a Windows PC does not work properly on a Linux server. --Since the locale setting of the application server is different between the development machine and the production machine, the NLS parameters also differ, and as a result, the same Java module operates differently between the development machine and the production machine.

The point is that Java applications can behave differently depending on the environment. If you are an experienced Java programmer, you should try programming that does not depend on DBMS or locale, but there is a reality that is not so (´ ・ ω ・ ｀)

Specific example: Implicit type conversion from string type to TIMESTAMP type

We conclude with a more specific example of how this behavior causes problems.

Suppose you have a table called ʻuserslike this: Let's say this table has a user namename and a record update time ʻupdated_at (which is a common configuration).

CREATE TABLE users (
  name VARCHAR2(256 CHAR),
  updated_at TIMESTAMP
)

The following Main2.java is a Java application that inserts data into this ʻusers` table.

Main2.java

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class Main2 {
    public static void main(String[] args) {
        String url = "jdbc:oracle:thin:@//192.168.99.100:1521/ORCLPDB1";
        String user = "dev1";
        String password = "password";

        try (Connection c = DriverManager.getConnection(url, user, password)) {
            PreparedStatement pstmt = c.prepareStatement("INSERT INTO users (name, updated_at) VALUES (?, ?)");
            pstmt.setString(1, "nekoTheShadow");
            pstmt.setString(2, "20200117");
            int count = pstmt.executeUpdate();

            System.out.printf("PreparedStatement::executeUpdate = %d%n", count);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

__ What you should pay attention to here is that you are trying to insert a string type value (" 20200117 ") into the TIMESTAMP type column ʻupdated_at. __ In this case, the "implicit type conversion" function of Oracle Database returns the string type to the TIMESTAMP` type. And how it is "implicitly converted" depends heavily on the NLS parameters.

Now, let's execute this Main2.java in a different locale. First of all, Japanese.

$ java -Duser.language=ja -Duser.country=JP -cp .:../lib/ojdbc8.jar Main2
PreparedStatement::executeUpdate = 1

It seems that the data has been inserted correctly. Then run it in English.

$ java -Duser.language=en -Duser.country=US -cp .:../lib/ojdbc8.jar Main2
java.sql.SQLDataException: ORA-01843: not a valid month

        at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:494)
        at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:446)
        at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1054)
        at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:623)
        at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:252)
        at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:612)
        at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:226)
        at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:59)
        at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:910)
        at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1119)
        at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3780)
        at oracle.jdbc.driver.T4CPreparedStatement.executeInternal(T4CPreparedStatement.java:1343)
        at oracle.jdbc.driver.OraclePreparedStatement.executeLargeUpdate(OraclePreparedStatement.java:3865)
        at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3845)
        at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate(OraclePreparedStatementWrapper.java:1061)
        at Main2.main(Main2.java:16)
Caused by: Error : 1843, Position : 51, Sql = INSERT INTO users (name, updated_at) VALUES (:1 , :2 ), OriginalSql = INSERT INTO users (name, updated_at) VALUES (?, ?), Error Msg = ORA-01843: not a valid month

        at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:498)
        ... 15 more

In the case of locale = Japan, it worked as expected, but as soon as I changed to locale = English, I got an Exception. What's more, it's hard to tell at a glance that the content of the Exception is rather confusing, or at least a locale-based problem. The solution is to not do implicit type conversion or use Java's Date type.