Database Software

Hi,

I'm using the XML DB features that provide access to PL/SQL Package procedures via a HTTP interface, listening on port 8080.
Within the stored procedure, that's invoked from the HTTP request, I write to an audit table containing a TIMESTAMP field supplying the CURRENT_TIMESTAMP pseudocolumn as the value.

CREATE TABLE PRCLOG
(
LOGDTE TIMESTAMP,
LOGSID NUMBER,
LOGSEQ NUMBER,
LOGPRC VARCHAR2(128),
LOGTXT VARCHAR2(4000),
)...

PROCEDURE WriteProcessLog(v_caller IN VARCHAR2, v_msg IN VARCHAR2)
AS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN -- Parent Transaction Suspended
INSERT INTO PRCLOG (LOGDTE, LOGSID, LOGPRC, LOGSEQ, LOGTXT)
VALUES (current_timestamp, sys_context('userenv','sid'), SUBSTR(v_caller, 1, 128), gLogIndex, DBTIMEZONE || '/' || SESSIONTIMEZONE || ':' || SUBSTR(v_msg, 1, 4000));
/* increment counter, wrap around at 64000 */
gLogIndex := mod(gLogIndex + 1, 64000);
/* commit autonomous transaction */
COMMIT;
END; -- Parent Transaction Resumes



Since i'm in England and currently within Daylight Savings Time, the timestamp column is showing a value of -1 hour when I view the data.
If I connect via SQLPlus and call the same stored procedure, then the value of the timestamp is correctly showing the BST time.

This is a problem, because I will write to the file from normal PL/SQL session and then fire off a URL call (via another external system) back into port 8080 to conclude the transaction.
When looking at the data in the table, the logging data pertaining to the port 8080 session is -1 hour as opposed to being the same timezone.

I've logged out to the table data the DBTIMEZONE and SESSIONTIMEZONE pseudocolumns and they appear as follows:
a) from the XML DB invocation: +00:00 and +00:00 (this is the wrong bit !!)
b) from normal SQL Plus invocation: +00:00/+01:00

Therefore my question is, how do I get the XML DB to use the correct timezone, taking into account daylight savings.
Also how does SQL Plus know that i'm in daylight savings mode ?

Here is my SQLPlus logon banner:
SQL*Plus: Release 11.2.0.1.0 Production on Wed Jun 5 17:00:26 2013

Connected to:
Oracle Database 11g Release 11.2.0.1.0 - 64bit Production

SQL> select dbtimezone, sessiontimezone from dual;

DBTIME SESSIONTIMEZONE
---------------------------------------------------------------------------
+00:00 +01:00


I've tried setting the environment variable ORA_STDZ as follows, in the script which starts oracle, but this has no effect.

ORA_STDZ='Europe/London'; export ORA_STDZ


I've also changed the column to LOGDTE TIMESTAMP WITH LOCAL TIME ZONE which solves the problem now, but when i'm looking at the data across GMT/BST boundaries it wouldn't look correct, since it would regress the times by -1 hours when I go back to GMT, so that's not acceptable.

many thanks