nls_date_format and jdbc

From: Dba DBA <oracledbaquestions_at_gmail.com>
Date: Tue, 23 Apr 2013 10:37:17 -0400
Message-ID: <CAE-dsOK82M6O9S33s0y_R6JGApra1tNy2zYh7YWA-bUkQpV=OQ_at_mail.gmail.com>

We have a varchar2 field that gets both character and date data inserted to it. We had set the nls_date_format in the database with the expectation that when the dates are 'to_char' they would all be changed to the same format. The developers did NOT set 'nls_date_format' in there jdbc connections and just inserted a 'sysdate'. This seemed to work in 'tomcat', but we get a different date conversion format when using IBM Websphere (the nls_date_format in the connection pool is null). The code changes would be extensive, so I am trying to find something I can do in the database
before saying we have to make a code change. To do that, i need to fully understand what is going on.

Here are some tests that we ran. I re-created the scenario using SQL Developer with a JDBC connection.

Test Scenario:

1. database nls_date_format: YYYY-MM-DD HH24:MI:SS
2. SQL Developer nls_date_format (tools-> database -> nls): YYYY-MON-DD HH24:MI:SS
3. Turned on auditing with db,extended. Audit all on my user account. To try to pick up all the SQLs to see if JDBC is issing an 'alter session set nls_date_format'

audit all by <my user> by access;

SQL:
create table test (mytest varchar2(20);

insert into test values (sysdate);
select * from test;

Output:
1. The date format was converted to YYYY-MON-DD HH24:MI:SS (nls_date_format set in sql developer). This overrides the nls_date_format set in the database.
2. Checked dba_audit_trail. I did not see an 'alter session set nls_date_format' issued by SQL Developer.

Question:

So how was the nls_date_format for the session changed if no alter session was issued? I actually changed the nls_date_format and reconnected to the database to see if the command is issued at connection. I did not see it.

2nd Test case: I used a logon trigger.

create table test_insert (username varchar2(30),sid number,mytest varchar2(300),insert_date date);
create or replace
TRIGGER LOGINTRG
AFTER LOGON ON DATABASE
BEGIN insert into test_insert select user, sys_context('USERENV','SID')
,value,sysdate from v$parameter where name = 'nls_date_format';
EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_FORMAT=''YYYY-MM-DD HH24:MI:SS''';
insert into test_insert select user, sys_context('USERENV','SID')
,value,sysdate from v$parameter where name = 'nls_date_format';
commit;
END LOGINTRG; issued from SQL Developer:

insert into test values (sysdate);

Output:
1. test insert had nls_date_format set to YYYY-MM-DD HH24:MI:SS' both times. When I change nls_date_format at the session level I see my local sessions nls_date_format.
2. My audit command did not record any of the 'alter session commands' issued by my trigger with an 'audit all by <my user> by access;.

Questions:
1. How come the alter session in my login trigger did not make it to the audit table? I thought audit all by user gets everything? 2. Is there anyway that I can keep a jdbc connection from overriding an nls_date_format? I might be able to do with this with a row level trigger on the table, but I prefer to not go there. Maybe something in the VPD?

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Apr 23 2013 - 16:37:17 CEST