In Oracle, why does current_timestamp sometimes return server time, other times GMT?

Question

I have a Java application running against an Oracle 9i database. The database has a trigger to create an audit record, which tracks changes to the base record. The trigger utilizes current_timestamp. When I modify the base record with the Java application, the audit record reflects GMT. However, if I use Toad and update the base record, the audit record reflects server time (which is set to local time). Querying current_timestamp returns server time. I can't seem to find what causes the difference. Any ideas out there?

Answer 1

NLS parameter values can be set within a session (sometimes in a login trigger), inherited from the OS environment, or set at instance startup, which makes tracking them down somewhat tricky.

The following query shows divergence from the database, and if you can get this output from your two diverging environments, it may help:

SELECT * FROM
(
SELECT PARAMETER, 
       VALUE AS SESSION_VALUE,
       (SELECT VALUE FROM NLS_INSTANCE_PARAMETERS NIP
         WHERE NIP.PARAMETER = NSP.PARAMETER) INSTANCE_VALUE,
       (SELECT VALUE FROM NLS_DATABASE_PARAMETERS NDP
         WHERE NDP.PARAMETER = NSP.PARAMETER) DATABASE_VALUE
  FROM NLS_SESSION_PARAMETERS NSP
)
 WHERE NVL(INSTANCE_VALUE, DATABASE_VALUE) <> DATABASE_VALUE
    OR NVL(SESSION_VALUE, NVL(INSTANCE_VALUE, DATABASE_VALUE)) <> DATABASE_VALUE

When I ran that on a Windows SQL*Plus, I got no rows returned, so there were no differences between my application environment and there database defaults. However, when I ran it from a Unix environment:

PARAMETER                 SESSION_VALUE             INSTANCE_VALUE            DATABASE_VALUE
------------------------- ------------------------- ------------------------- -------------------------
NLS_DATE_FORMAT           YYYY-MM-DD HH24:MI:SS                               DD-MON-RR
NLS_TIMESTAMP_TZ_FORMAT   YYYY-MM-DD HH24:MI:SS.FF                            DD-MON-RR HH.MI.SSXFF AM
                          TZH:TZM                                             TZR