Use JDBC to change the session parameter NLS_DATE_FORMAT and oraclenlslang of Oracle

Use JDBC to change the session parameter NLS_DATE_FORMAT and oraclenlslang 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.

In oracle, what is the problem of alter session set nls_date_format?

Session is the basic information related to the connection to the database.

Alter session set nls_date_format = 'yyyy-dd -- mm'

Is to modify the date display format of this session.

This modification only modifies the logon status.
Do not modify other sessions.

If you log out, the information will be lost.
After you log on again, it is a new Session.

The following is an example of a test:

D: \> sqlplus test/test @ testdb
SQL * Plus: Release 9.2.0.1.0-Production on Monday April 1 09:46:43 2013
Copyright (c) 1982,200 2, Oracle Corporation. All rights reserved.
Connect:
Oracle Database 10g Express Edition Release 10.2.0.1.0-Production

-- Date of the first query
SQL> select sysdate from dual;
SYSDATE
----------
Month 1-4-13

-- Modify the date display format of a session
SQL> alter session set nls_date_format = 'yyyy-dd-mm'
2;
The session has been changed.

-- Query date

SQL> select sysdate from dual;
SYSDATE
----------
2013-01-04

-- Reconnect once.
SQL> conn test/test @ testdb
Connected.

-- Query the date again, and the Session settings are reset.
SQL> select sysdate from dual;
SYSDATE
----------
Month 1-4-13
SQL>

Oracle nls_date_format