Different results in Toad vs SQLPLUS when selecting datatype TIMESTAMP WITH LOCAL TIMEZONE (4242796)

Different results in Toad vs SQLPLUS when selecting datatype TIMESTAMP WITH LOCAL TIMEZONE

Setting the session time_zone does not function correctly in TOAD 10.1.0.7. There are different results when selecting datatype TIMESTAMP WITH LOCAL TIMEZONE via TOAD vs sqlplus.

STEPS TO REPRODUCE:
FROM TOAD:

SQL> CREATE TABLE TEST_TZ
(
TEST_ID NUMBER(4) DEFAULT 0,
TRANS_DATE TIMESTAMP(2) WITH LOCAL TIME ZONE,
CREATE_DATE TIMESTAMP(2) WITH LOCAL TIME ZONE
);

select dbtimezone, sessiontimezone from dual;

+00:00 -06:00

Insert into TEST_TZ VALUES
(101,TO_TIMESTAMP_TZ(10/31/2009 9:59:33.93 AM,fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM),
TO_TIMESTAMP_TZ (11/02/2009 9:59:33.93 AM,fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM));


select * from test_tz;

101 10/31/2009 9:59:33:93 AM 11/2/2009 9:59:33.93 AM


alter session set time_zone = America/Chicago;

select dbtimezone, sessiontimezone from dual;

+00:00 America/Chicago

Insert into TSAPP_LOCAL.TEST_TZ VALUES
(102,TO_TIMESTAMP_TZ(10/31/2009 9:59:33.93 AM,fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM),
TO_TIMESTAMP_TZ (11/02/2009 9:59:33.93 AM,fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM));

select * from test_tz;

101 10/31/2009 9:59:33:93 AM 11/2/2009 9:59:33.93 AM
102 10/31/2009 8:59:33:93 AM 11/2/2009 9:59:33.93 AM

Now perform selects from SQLPLUS:
SQL> select dbtimezone, sessiontimezone from dual;
+00:00 -06:00

SQL> select * from tsapp_local.test_tz1;

101 31-OCT-09 09.59.33.93 AM 02-NOV-09 09.59.33.93 AM
102 31-OCT-09 08.59.33.93 AM 02-NOV-09 09.59.33.93 AM

SQL> select dbtimezone, sessiontimezone from dual;

+00:00 America/Chicago

SQL> select * from tsapp_local.test_tz1;

101 31-OCT-09 10.59.33.93 AM 02-NOV-09 09.59.33.93 AM
102 31-OCT-09 09.59.33.93 AM 02-NOV-09 09.59.33.93 AM


Note the differences in the select output....

WORKAROUND:
None

STATUS:
Waiting for fix in a future release of Toad for Oracle.

ST68812