I've run into a change in behavior between 9i and 10g that is causing me trouble. I'm using Oracle 9.2.0.7.0 and 10.2.0.1.0. They are running on different Windows 2003 servers that is patched as they come out so it is up to date as of '2007-01-24'.
The application that is expecting to get some XML back from the database expects the full date in a form like '2007-01-24 13:01:00'. As long as the calling application does something like the following the dates returned include the times also as long as we are on 9i.
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
I began to convert to 10g last week and all was going well until a developer began testing the application. The dates now only contain the date part. I have a four line test script that I execute from SQL*Plus I'll paste below.
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
select xmlelement("date", sysdate).getclobval() from dual;
select Auth_Date from Trans where Trans_Id = 63;
select xmlelement("Auth_Date", Auth_Date).getclobval() from Trans where Trans_Id = 63;
The results of running this script on 10g are pasted below.
sys@DEVCLN> @Test.sql
Session altered.
XMLELEMENT("DATE",SYSDATE).GETCLOBVAL()
--------------------------------------------------------------------------------
<date>2007-01-24 13:05:13</date>
AUTH_DATE
-------------------
2007-01-24 08:01:17
XMLELEMENT("AUTH_DATE",AUTH_DATE).GETCLOBVAL()
--------------------------------------------------------------------------------
<Auth_Date>2007-01-24</Auth_Date>
sys@DEVCLN>
As you can see the date is stored correctly and returns correctly with a simple select. Also note that the select from dual returns the correct format. But, as soon as I select from a real table I don't get what I expect. In 9i I obtain the desired results and don't have a problem.
I've been scrounging through the forums, web sites, and documentation but so far have failed to find the answer. My boss started breathing down my neck so I thought the time to cry for help had arrived. Do you know or have any idea why I can't get my dates to format properly in 10g?