Jess, there is an offer to attack a problem under a correct angle :),
1. I have created two fields
- TIMESTAMP(6) WITH TIME ZONE
- TIMESTAMP(6) WITH LOCAL TIME ZONE
2. Has changed xml file. Instead of sql has written stored procedure
<parameterMap id="timeZoneParameters" class="Map">
<parameter property="timezone" jdbcType="VARCHAR" javaType="
java.lang.String" mode="INOUT" />
</parameterMap>
<procedure id="setTimeZone" parameterMap="timeZoneParameters">
{ call projects.tz_set_and_show( ? ) }
</procedure>
3. Stored procedure to set and show time zone .
CREATE OR REPLACE PROCEDURE tz_set_and_show (tz_in IN VARCHAR2 := null)
IS
BEGIN
IF tz_in IS NOT NULL
THEN EXECUTE IMMEDIATE 'alter session set time_zone = ''' ||
tz_in || '''' ;
END IF;
-- DBMS_OUTPUT.put_line ( 'SESSIONTIMEZONE = '|| SESSIONTIMEZONE )
;
-- DBMS_OUTPUT.put_line ( 'CURRENT_TIMESTAMP = '|| CURRENT_TIMESTAMP
) ;
-- DBMS_OUTPUT.put_line ( 'LOCALTIMESTAMP = ' || LOCALTIMESTAMP ) ;
-- DBMS_OUTPUT.put_line ( 'SYS_EXTRACT_UTC (LOCALTIMESTAMP) = '||
sqlexpr('SYS_EXTRACT_UTC (LOCALTIMESTAMP)') );
END ;
4. Model .
- DAO set timezone 'America/Denver'
- DAO getdata
- DAO set timezone 'Turkey'
- DAO getdata
5. Results
DEBUG [http-8080-Processor23] java.sql.PreparedStatement debug-
{pstm-100022} PreparedStatement: { call projects.tz_set_and_show( ? ) }
DEBUG [http-8080-Processor23] java.sql.PreparedStatement debug-
{pstm-100022} Parameters: [America/Denver]
Result [ 18-МАР-08 06.19.40,000000 PM +03:00, *** 18-МАР-08 08.19.40,000000
AM, 8 ***]
DEBUG [http-8080-Processor23] java.sql.PreparedStatement debug-
{pstm-100027} PreparedStatement: { call projects.tz_set_and_show( ? ) }
DEBUG [http-8080-Processor23] java.sql.PreparedStatement debug-
{pstm-100027} Parameters: [Turkey]
Result: [ 18-МАР-08 06.19.40,000000 PM +03:00, *** 18-МАР-08 05.19.40,000000
PM, 8 ***]
Andrey .
2008/3/18, Jesse Reimann <[EMAIL PROTECTED]>:
>
> Thanks for the suggestion, but that didn't help since it is the actual
> oracle jdbc driver that is throwing the exception and just setting the
> session within oracle doesn't stop prevent this.
>
>
>
> I think I'm just going to abandon this approach and attack the problem
> from a different angle and handle all my time zone conversion stuff
> explicitly within Oracle. Thinking about it more setting a connection level
> local time zone won't solve the business need since we need to allow the
> time zone to be changed on the individual SQL statement execution basis.
>
>
>
>
>
> Jesse
>
>
> ------------------------------
>
> *From:* Andrey Rogov [mailto:[EMAIL PROTECTED]
> *Sent:* Monday, March 17, 2008 9:43 PM
> *To:* [email protected]
> *Subject:* Re: Retrieving Oracle column with data type TIMESTAMP WITH
> LOCAL TIME ZONE
>
>
>
> Jess,
> try to execute sql operator ALTER SESSION before accessing TIMESTAMP
> WITH LOCAL TIME ZONE data
>
> <update id="setTimeZone" parameterClass="java.lang.String">
> ALTER SESSION SET time_zone = #timezonevalue#
> </update>
>
>
>
>
>
> 2008/3/17, Jesse Reimann <[EMAIL PROTECTED]>:
>
> I'm trying to retrieve an Oracle column that is defined as TIMESTAMP WITH
> LOCAL TIME ZONE. Currently I'm receiving an error stating
>
>
>
> --- Cause: *java.sql.SQLException*: Session Time Zone not set!
>
>
>
> Looking into it I need to specify the Session Time Zone as part of the
> Oracle JDBC Connection properties.
>
>
>
> I found this in an Oracle document when searching for a solution:
>
>
>
> Before accessing TIMESTAMP WITH LOCAL TIME ZONE data, call the
> OracleConnection.setSessionTimeZone(String regionName) method to set the
> session time zone. When this method is called, the JDBC driver sets the
> session time zone of the connection and saves the session time zone so that
> any TIMESTAMP WITH LOCAL TIME ZONE data accessed through JDBC can be
> adjusted using the session time zone.
>
>
>
>
>
> So my question is how would I go about calling the setSessionTimeZone
> method of the OracleConnection when using iBATIS (and Spring).
>
>
>
> Thanks,
>
>
>
> Jesse Reimann
>
>
>
