Thanks Tim :). Although it seems to have fixed the problem. But there is some problem(i guess a oracle bug) in utilizing this feature with Oracle 8i client. Although with 9i client it works just fine. Our applications are written in PERL5.6.1 uses DBI(Oracle 8.1.7) but the database/schema is on Oracle 9i.
I was wondering if we had DBI for Oracle 9i available that would have fixed this or may be not. Do any of you know when that's happening as oracle 9i is out there for a while(2 or so years)? The problem is as below. I have put this on oracle discussion forum too : ****************************************************** TIMESTAMP WITH LOCAL TIME ZONE doesn't return correct time zone value using oracle 8i client on a 9i database. Platform : Solaris 2.8 on Sun Sparc architecture. Client : Oracle 8.1.7.0.0. Database : Oracle 9.2.0.4.0 I have a database table with a field of type "TIMESTAMP WITH LOCAL TIME ZONE" on 9i database. When i do a select query on this field using SQL-Plus 8.1.7 client after setting the session time zone value to a value different then the database time zone, the correct time zone value is not returned. Following will make it more clear: $ echo $ORACLE_HOME /u01/app/oracle/product/8.1.7 $ $ORACLE_HOME/bin/sqlplus [EMAIL PROTECTED] SQL*Plus: Release 8.1.7.0.0 - Production on Mon Oct 20 11:36:50 2003 (c) Copyright 2000 Oracle Corporation. All rights reserved. Enter password: Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.4.0 - Production SQL> select sessiontimezone from dual; SESSIONTIMEZONE --------------------------------------------------------------------------- -06:00 SQL> desc date_test; Name Null? Type ----------------------------------------- -------- ---------------------------- D_DATE TIMESTAMP(6) WITH LOCAL TIME ZONE D_TSLTZ TIMESTAMP(6) WITH LOCAL TIME ZONE SQL> select * from date_test; D_DATE --------------------------------------------------------------------------- D_TSLTZ --------------------------------------------------------------------------- 17-OCT-03 03.43.01 PM 17-OCT-03 03.43.01 PM SQL> alter session set time_zone = '+01:00'; Session altered. SQL> select * from date_test; D_DATE --------------------------------------------------------------------------- D_TSLTZ --------------------------------------------------------------------------- 17-OCT-03 03.43.01 PM 17-OCT-03 03.43.01 PM If i change my ORACLE_HOME to 9.2.0, and do the same query after changing the session time zone value to '+01:00', it works just fine. $ export ORACLE_HOME=/u01/app/oracle/product/9.2.0 $ echo $ORACLE_HOME /u01/app/oracle/product/9.2.0 $ $ORACLE_HOME/bin/sqlplus [EMAIL PROTECTED] SQL*Plus: Release 9.2.0.4.0 - Production on Mon Oct 20 11:47:17 2003 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Enter password: Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.4.0 - Production SQL> select sessiontimezone from dual; SESSIONTIMEZONE --------------------------------------------------------------------------- -06:00 SQL> select * from date_test; D_DATE --------------------------------------------------------------------------- D_TSLTZ --------------------------------------------------------------------------- 17-OCT-03 03.43.01.000000 PM 17-OCT-03 03.43.01.000000 PM SQL> alter session set time_zone = '+01:00' 2 ; Session altered. SQL> select * from date_test; D_DATE --------------------------------------------------------------------------- D_TSLTZ --------------------------------------------------------------------------- 17-OCT-03 10.43.01.000000 PM 17-OCT-03 10.43.01.000000 PM ****************************************************** This is defintely seems to be some oracle problem but donot know if we had dbi for 9i that would have solved this problem. Any ideas??.... Sorry if i have disturbed peace on this group's list. Thanks for taking time to read this and for your responses in advance. Deepak. -----Original Message----- From: Tim Bunce [mailto:[EMAIL PROTECTED] Sent: Saturday, October 18, 2003 8:36 AM To: Gaur, Deepak Cc: '[EMAIL PROTECTED]'; [EMAIL PROTECTED] Subject: Re: DBI fetch gets SEGEMENTATION FAULT with new ORACLE date data type(TIMESTAMP WITH LOCAL TIME ZONE). There is indeed such a plan. Tim. On Fri, Oct 17, 2003 at 02:29:48PM -0600, Gaur, Deepak wrote: > Hello Tim, > > This seems to have fixed the problem. > Is there a plan to patch it with the next DBD release? > > Thanks for your time. > Deepak. > > -----Original Message----- > From: Andy Hassall [mailto:[EMAIL PROTECTED] > Sent: Thursday, October 16, 2003 4:37 PM > To: Gaur, Deepak; [EMAIL PROTECTED] > Subject: Re: DBI fetch gets SEGEMENTATION FAULT with new ORACLE date > data type (TIMESTAMP WITH LOCAL TIME ZONE). > > > Gaur, Deepak wrote: > > Hello To All DBI Users :-), > > > > I was trying to use DBI with a new oracle date data type called > > "TIMESTAMP WITH LOCAL TIME ZONE" but the code comes back with a > > "Segmentation Fault(core dumped)" error while trying to fetch data > > from the cursor. > > > > I was just wondering if these new data types are supported in DBI yet > > or I am doing something wrong or there is a different way of handling > > these new datatypes. > > > > I was thinking to migrate all my oracle DB tables using DATE data > > type to TIMESTAMP WITH LOCAL TIME ZONE, so that i can use the > > time_zone session attribute from oracle to get the right local time > > for lot of our international clients. I have our application code in > > both PERL and Java. I m not able to figure out how i can make it to > > work for perl 5.6.1, although Java code seems to accept this new > > date data type without any problems with the new JDBC driver. > > > > Is it just me or is it a known problem? > > See this previous thread: > > http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&threadm=20030514 > 214011.7170D29873%40chronozon.artofdns.com&rnum=4&prev=/&frame=on > > (patch attached to end of my reply, the 2nd message in that thread) > > -- > Andy Hassall ([EMAIL PROTECTED]) icq(5747695) (http://www.andyh.co.uk) > Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)
