Use JDBC to change the session parameters of Oracle Nls_date_format

Recent projects in addition to a problem, the scenario is probably this, the project was developed abroad, in the project development process using a large number of Oracle function To_date, but the developer did not write a second parameter.

So the project in the foreign server can be normal operation, but in the domestic server will not work properly.

By investigation, because the first parameter of the To_date function is YYYYMMDD and the second argument is null, the Nls_date_format parameter in session is used by default.

The database server is in English, so the default Nls_date_format is RR-MM-DD, and the client's parameters are used when connecting to the Oracle database server abroad

Overrides the server-side parameters. (This is based on Oracle's official documentation)

This initial value was overridden by a Client-side value if the client uses the Oracle JDBC driver or if the client is oci- Based and the Nls_lang client setting (environment variable) is defined. The initialization parameter value is, therefore, usually ignored.

However, the official document does not say how to do, so check the information, in the registry Software "Oracle under the key starting with the key under the string Value, Nls_date_format=yyyymmdd.

The experiment proves that this modification is valid for sqlplus, but it is not valid for Sqldeveloper. No effective methods for sqldeveloper have been found yet.

Second, for JDBC also did not find the way to set parameters, if you have a good way to give me a message.

But then again, I found a way to solve this problem, which is to create a trigger.

I created the following trigger:

CREATE OR REPLACE TRIGGER LOGINTRG
After LOGON on DATABASE
BEGIN
EXECUTE IMMEDIATE ' ALTER SESSION SET nls_date_format= ' rr/mm/dd ';
END LOGINTRG;

Note that SQL does not execute if the date is not formatted.

Use JDBC to change the session parameters of Oracle Nls_date_format