Hitachi Vantara Pentaho Community Forums
Results 1 to 3 of 3

Thread: How to transform 'TIMESTAMP(6) WITH LOCAL TIME ZONE' datatype in oracle directly?

  1. #1

    Default How to transform 'TIMESTAMP(6) WITH LOCAL TIME ZONE' datatype in oracle directly?

    Hi All,
    I'm using both oracle as my source database and target database,In my source database I have a table named orders and have a column order_date with datatype 'TIMESTAMP(6) WITH LOCAL TIME ZONE' ,In my target database I have a table named TIMES have a column timestamp comes from many other tables.
    When I want to transform the order_date from orders to TIMES directly I got an error said that 'Session Time Zone not set!' ,It's a certain oracle error because the client didn't set timestamp data format,so my current solution is like 'SELECT TO_TIMESTAMP(TO_CHAR(ORDER_DATE)) FROM ORDERS' , but I'm wondering how can I transform them directly?

    I'm currently using kettle 3.0r2, but it seems that it could occur on any platform,below is my error log message:
    2007/09/19 11:18:47 - table input .0 - ERROR (version 3.0.0-M2, build 252004 from 2007/09/06 01:03:58) : Unexpected error :
    2007/09/19 11:18:47 - table input .0 - ERROR (version 3.0.0-M2, build 252004 from 2007/09/06 01:03:58) : org.pentaho.di.core.exception.KettleDatabaseException:
    2007/09/19 11:18:47 - table input.0 - ERROR (version 3.0.0-M2, build 252004 from 2007/09/06 01:03:58) : Couldn't get row from result set
    2007/09/19 11:18:47 - table.0 - ERROR (version 3.0.0-M2, build 252004 from 2007/09/06 01:03:58) : Session Time Zone not set!
    2007/09/19 11:18:47 - table input.0 - ERROR (version 3.0.0-M2, build 252004 from 2007/09/06 01:03:58) :
    2007/09/19 11:18:47 - table input.0 - ERROR (version 3.0.0-M2, build 252004 from 2007/09/06 01:03:58) : at org.pentaho.di.core.database.Database.getRow(Database.java:2630)
    2007/09/19 11:18:47 - table input .0 - ERROR (version 3.0.0-M2, build 252004 from 2007/09/06 01:03:58) : at org.pentaho.di.core.database.Database.getRow(Database.java:2550)
    2007/09/19 11:18:47 - table input .0 - ERROR (version 3.0.0-M2, build 252004 from 2007/09/06 01:03:58) : at org.pentaho.di.core.database.Database.getRow(Database.java:2524)
    2007/09/19 11:18:47 - table input .0 - ERROR (version 3.0.0-M2, build 252004 from 2007/09/06 01:03:58) : at org.pentaho.di.trans.steps.tableinput.TableInput.doQuery(TableInput.java:208)
    2007/09/19 11:18:47 - table input .0 - ERROR (version 3.0.0-M2, build 252004 from 2007/09/06 01:03:58) : at org.pentaho.di.trans.steps.tableinput.TableInput.processRow(TableInput.java:121)
    2007/09/19 11:18:47 - table input .0 - ERROR (version 3.0.0-M2, build 252004 from 2007/09/06 01:03:58) : at org.pentaho.di.trans.steps.tableinput.TableInput.run(TableInput.java:302)
    2007/09/19 11:18:47 - table input .0 - ERROR (version 3.0.0-M2, build 252004 from 2007/09/06 01:03:58) : Caused by: java.sql.SQLException: Session Time Zone not set!
    2007/09/19 11:18:47 - table input .0 - ERROR (version 3.0.0-M2, build 252004 from 2007/09/06 01:03:58) : at oracle.jdbc.driver.TimestampltzAccessor.getString(TimestampltzAccessor.java:88)
    2007/09/19 11:18:47 - table input .0 - ERROR (version 3.0.0-M2, build 252004 from 2007/09/06 01:03:58) : at oracle.jdbc.driver.T4CTimestampltzAccessor.getString(T4CTimestampltzAccessor.java:254)
    2007/09/19 11:18:47 - table input .0 - ERROR (version 3.0.0-M2, build 252004 from 2007/09/06 01:03:58) : at oracle.jdbc.driver.OracleResultSetImpl.getString(OracleResultSetImpl.java:397)
    2007/09/19 11:18:47 - table input .0 - ERROR (version 3.0.0-M2, build 252004 from 2007/09/06 01:03:58) : at org.pentaho.di.core.database.Database.getRow(Database.java:2583)
    2007/09/19 11:18:47 - table input .0 - ERROR (version 3.0.0-M2, build 252004 from 2007/09/06 01:03:58) : ... 5 more

    Thanks
    Alex

  2. #2
    Join Date
    May 2006
    Posts
    4,882

    Default

    It's an oracle problem that they don't take the default system timezone into account. You can try to execute the sql statements in the database connection dialog (there's a tab with statements that will be executed upon opening the connection).

    I had this problem before, at that time the only way out was to do an ugly Oracle-only fix... but since I threw "timestamp with local timezone" out of the window I killed the bug-tracker.

    Now first read the Oracle fine print of "timestamp with local timezone", and get very freightened. The replacement of it is similar to how Java does it. Store timestamps in a certain fixed timezone (e.g. UTC) and convert all dates on INPUT to that timezone.

    Regards,
    Sven

    P.S. And don't use v3 yet for anything productively.... try to make a new job from scractch e.g.
    Last edited by sboden; 09-19-2007 at 04:08 AM.

  3. #3

    Default

    Yes , you are right.
    This is a more smart way to solve the problem,
    Thanks
    Alex

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.