Use JDBC to change the session parameter NLS_DATE_FORMAT of Oracle

Use JDBC to change the session parameter NLS_DATE_FORMAT of Oracle

Except for one problem recently, this is probably the case. The project is developed abroad and a large number of Oracle functions TO_DATE are used in the project development process, but the developer did not write the second parameter.

So the project can run normally on foreign servers, but it cannot run normally on domestic servers.

The investigation shows that the first parameter of the TO_DATE function is YYYYMMDD. If the second parameter is null, The NLS_DATE_FORMAT parameter in the session is used by default.

The database server uses the English version, so the default NLS_DATE_FORMAT is the RR-MM-DD, and when connecting to the Oracle database server abroad, will use the client Parameters

Overwrite server parameters. (This is based on the official Oracle document)

This initial value is 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 show how to do this. Therefore, we have added String Value and NLS_DATE_FORMAT = YYYYMMDD to the key starting with key in Software in the registry.

Experiments show that this modification is effective for SQLPLUS, but not for SQLDeveloper. Currently, no methods are available for SQLDeveloper.

Second, no way to set parameters is found for JDBC. If you have any good methods, leave a message for me.

However, I found a way to solve this problem, that is, to create a trigger.

The trigger I created is as follows:

CREATE OR REPLACE TRIGGER LOGINTRG
AFTER LOGON DATABASE
BEGIN
Execute immediate 'alter session set NLS_DATE_FORMAT = ''rr/MM/dd ''';
End logintrg;

Note that if the date format is incorrect, the SQL statement will not be executed.