topic Re: Connection to Oracle 10g in Talend Studio

Connection to Oracle 10g

Anonymous — Sat, 16 Nov 2024 13:15:18 GMT

When I try to connect to a Oracle 10g database and want to retrieve data from it, I got the following error message:
connecting to socket on port 3981
connected
Exception in component tOracleInput_1
java.sql.SQLException: Session Time Zone not set!
at oracle.jdbc.driver.TimestampltzAccessor.getString(TimestampltzAccessor.java:88)
at oracle.jdbc.driver.T4CTimestampltzAccessor.getString(T4CTimestampltzAccessor.java:254)
at oracle.jdbc.driver.OracleResultSetImpl.getString(OracleResultSetImpl.java:397)
at datawarehouse_bic.sessiontimezone_0_1.sessionTimeZone.tOracleInput_1Process(sessionTimeZone.java:1578)
at datawarehouse_bic.sessiontimezone_0_1.sessionTimeZone.runJobInTOS(sessionTimeZone.java:2408)
at datawarehouse_bic.sessiontimezone_0_1.sessionTimeZone.main(sessionTimeZone.java:2282)
disconnected
If I check the connection towards the database, its succesful.
The table I want to retreive has several date fields where (in Oracle) the type is set to TIMESTAMP_6_WITH LOCAL TIME ZONE
Do I need to do something special to retreive the date and not get the error.
I am using TOS 4.0.2
Thanks
Henk

Re: Connection to Oracle 10g

Anonymous — Tue, 12 Oct 2010 03:28:26 GMT

Hello
Here is a related problem about IMESTAMP_6_WITH LOCAL TIME ZONE
http://www.orafaq.com/usenet/comp.databases.oracle.misc/2005/12/06/0081.htm
Try to execute this query in tOracleRow component, eg:
tOracleConnection
|
onsubjobok
|
tOracleRow
|
onsubjobok
|
tOracleInput---tLogRow
|
tOracleCommit.
on tOracleRow, using an existing connection, execute the query: "alter session set time_zone='<TZ>'" //TZ is your time zone, you can get it by TimeZone.getDefault()
on tOracleInput: using an existing connection.
Best regards
Shong

Re: Connection to Oracle 10g

Anonymous — Tue, 12 Oct 2010 14:31:45 GMT

In perl, you can do this by using a tPerl at the start of the job where you initialize the oracle env varibles:

$ENV{'TZ'} = 'PST';