I know, I know, I’m behind in my upgrades - sorry! This might already be
fixed in the 11 betas, however…
Apologies for the following length …
Summary:
- Why does F5 crash Toad?
- Why does Toad & QSR display Timestamp with local time zone
incorrectly for different session time zones?
Evidence:
Toad 10.5.1.3.
Oracle 10.2.0.4 database running on 32 bit Suse Linux Enterprise Server.
Oracle 10.2.0.1 client. (From Oracle XE).
set lines 400
set trimspool ON
– Make sure we can see the data.
ALTER SESSION set NLS_TIMESTAMP_TZ_FORMAT=‘dd/mm/yyyy hh24:mi:ssxff
TZH:TZM’ ;
ALTER SESSION set NLS_TIMESTAMP_FORMAT=‘dd/mm/yyyy hh24:mi:ssxff’ ;
– Clean up.
DROP TABLE mytimetable;
– Create a table to store the timestamp info.
CREATE TABLE mytimetable (
ts TIMESTAMP,
tz TIMESTAMP WITH TIME ZONE,
ltz TIMESTAMP WITH LOCAL TIME ZONE);
– Make sure we are correctly timezoned.
ALTER SESSION set TIME_ZONE = ‘Europe/London’;
– Check.
SELECT sessiontimezone FROM dual;
– Insert some data.
INSERT INTO mytimetable VALUES(
current_timestamp, current_timestamp, current_timestamp);
COMMIT;
– Retrieve the data in London.
SELECT ltz FROM mytimetable;
– Alter the session’s time zone.
ALTER SESSION set TIME_ZONE=‘Australia/Brisbane’;
– Check.
SELECT sessiontimezone FROM dual;
– Retrieve the data in Brisbane.
SELECT ltz FROM mytimetable;
– Reset.
ALTER SESSION set TIME_ZONE = ‘Europe/London’;
Problem 1:
When I run each SQL statement above, using F9, it all works fine except
the date displayed for the Brisbane LTZ column is still showing as if we
were in London:
…
– Retrieve the data in London.
SELECT LTZ FROM mytimetable;
14/10/2010 15:09:16.674535
^^^^^^^^
…
– Retrieve the data in Brisbane.
SELECT ltz FROM mytimetable;
14/10/2010 15:09:16.674535
^^^^^^^^
As you can see, both display the same date, regardless of the session
time zone.
Problem 2:
Running as a script bombs out horribly:
…
SESSIONTIMEZONE