tags:

views:

4253

answers:

2

I have defined the global nls_date_format on Oracle 10.2 XE as follows:

alter system set nls_date_format='YYYY-MM-DD HH24:MI:SS' scope=spfile;

When connecting on Windows, the clients override it with session specific format, so I need to run this line at the beginning of every session:

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

However, I have some custom code that I can't change (jdbc code, using ojdbc14.jar), so I can't execute this line when receiving the connection. Is there a way to change the default value of nls_date_format for all jdbc connections? Perhaps adding something to the connection string, or some environment variable that I can use?

By the way, sqlplus and sqldeveloper also override the server's format with their own, but I found out how to change their defaults, so the problem is only with jdbc connections.

+5  A: 

Set nls date format in an after logon trigger

+1  A: 

Thanks, that worked for me. The trigger that I inserted is this:

CREATE OR REPLACE TRIGGER LOGINTRG
AFTER LOGON ON DATABASE
BEGIN
EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_FORMAT=''YYYY-MM-DD HH24:MI:SS''';
END LOGINTRG;
Yoni