Hi,

I am trying to connect Sqoop (Sqoop 1.3.0-cdh3u3) to Oracle 8 using the jar
file (classes12.jar placed in /usr/lib/sqoop/lib folder). I am not able to
retrieve the data from the table as it gives me the error "Unable to set
the Timezone". Here is more info:

Command1: --> Could not set Timezone error
*sqoop list-tables --connect jdbc:oracle:thin:@nn.nn.nn.nn:1521/XXXXXX
--username xxxxxxx --password zzzzzzzzz --verbose*
12/05/08 12:55:49 DEBUG tool.BaseSqoopTool: Enabled debug logging.
12/05/08 12:55:49 WARN tool.BaseSqoopTool: Setting your password on the
command-line is insecure. Consider using -P instead.
12/05/08 12:55:49 DEBUG sqoop.ConnFactory: Loaded manager factory:
com.cloudera.sqoop.manager.DefaultManagerFactory
12/05/08 12:55:49 DEBUG sqoop.ConnFactory: Trying ManagerFactory:
com.cloudera.sqoop.manager.DefaultManagerFactory
12/05/08 12:55:49 DEBUG manager.DefaultManagerFactory: Trying with scheme:
jdbc:oracle:thin:@nn.nn.nn.nn
12/05/08 12:55:49 DEBUG manager.OracleManager$ConnCache: Instantiated new
connection cache.
12/05/08 12:55:49 INFO manager.SqlManager: Using default fetchSize of 1000
12/05/08 12:55:49 DEBUG sqoop.ConnFactory: Instantiated ConnManager
com.cloudera.sqoop.manager.OracleManager@70cb6009
12/05/08 12:55:49 DEBUG manager.OracleManager: Creating a new connection
for jdbc:oracle:thin:@nn.nn.nn.nn:1521/XXXXXX, using username: xxxxxxx
12/05/08 12:55:49 DEBUG manager.OracleManager: No connection paramenters
specified. Using regular API for making connection.
12/05/08 12:55:50 WARN manager.OracleManager: Time zone GMT could not be
set on Oracle database.
12/05/08 12:55:50 INFO manager.OracleManager: Setting default time zone: GMT
12/05/08 12:55:50 ERROR manager.OracleManager: Could not set time zone for
oracle connection
java.lang.reflect.InvocationTargetException
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
        at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
        at java.lang.reflect.Method.invoke(Method.java:597)
        at
com.cloudera.sqoop.manager.OracleManager.setSessionTimeZone(OracleManager.java:365)
        at
com.cloudera.sqoop.manager.OracleManager.makeConnection(OracleManager.java:321)
        at
com.cloudera.sqoop.manager.GenericJdbcManager.getConnection(GenericJdbcManager.java:53)
        at
com.cloudera.sqoop.manager.OracleManager.listTables(OracleManager.java:688)
        at
com.cloudera.sqoop.tool.ListTablesTool.run(ListTablesTool.java:51)
        at com.cloudera.sqoop.Sqoop.run(Sqoop.java:146)
        at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:65)
        at com.cloudera.sqoop.Sqoop.runSqoop(Sqoop.java:182)
        at com.cloudera.sqoop.Sqoop.runTool(Sqoop.java:221)
        at com.cloudera.sqoop.Sqoop.runTool(Sqoop.java:230)
        at com.cloudera.sqoop.Sqoop.main(Sqoop.java:239)
Caused by: java.sql.SQLException: ORA-01874: time zone hour must be between
-12 and 13

Command2:  --> Successfully lists tables
*sqoop list-tables --driver oracle.jdbc.OracleDriver **--connect
jdbc:oracle:thin:@nn.nn.nn.nn:1521/XXXXXX --username xxxxxxx --password
zzzzzzzzz --verbose*
12/05/08 13:03:37 DEBUG tool.BaseSqoopTool: Enabled debug logging.
12/05/08 13:03:37 WARN tool.BaseSqoopTool: Setting your password on the
command-line is insecure. Consider using -P instead.
12/05/08 13:03:37 DEBUG sqoop.ConnFactory: Loaded manager factory:
com.cloudera.sqoop.manager.DefaultManagerFactory
12/05/08 13:03:37 DEBUG sqoop.ConnFactory: Trying ManagerFactory:
com.cloudera.sqoop.manager.DefaultManagerFactory
12/05/08 13:03:37 INFO manager.SqlManager: Using default fetchSize of 1000
12/05/08 13:03:37 DEBUG sqoop.ConnFactory: Instantiated ConnManager
com.cloudera.sqoop.manager.GenericJdbcManager@a39ab89
12/05/08 13:03:37 DEBUG manager.SqlManager: No connection paramenters
specified. Using regular API for making connection.
GENERIC_LIST
GENERIC_LIST_ITEM_50
GENERIC_LIST_ITEM_500
GENERIC_LIST_ITEM_500_BKP

Command3:  --> When I use the above parameters in the options file and
create a sqoop job and execute it, I get "SQL command not properly ended"
error.
*sqoop-job --meta-connect
jdbc:hsqldb:hsql://xxxxxxxx.xxxx.com:16000/sqoop--exec
* *zzzzzzzz* *--verbose*
12/05/08 13:07:02 DEBUG tool.JobTool: Enabled debug logging.
12/05/08 13:07:02 DEBUG hsqldb.HsqldbJobStorage: Checking for table:
SQOOP_ROOT
12/05/08 13:07:02 DEBUG hsqldb.HsqldbJobStorage: Found table: SQOOP_ROOT
12/05/08 13:07:02 DEBUG hsqldb.HsqldbJobStorage: Looking up property
sqoop.hsqldb.job.storage.version for version null
12/05/08 13:07:02 DEBUG hsqldb.HsqldbJobStorage:  => 0
12/05/08 13:07:02 DEBUG hsqldb.HsqldbJobStorage: Looking up property
sqoop.hsqldb.job.info.table for version 0
12/05/08 13:07:02 DEBUG hsqldb.HsqldbJobStorage:  => SQOOP_SESSIONS
12/05/08 13:07:02 DEBUG hsqldb.HsqldbJobStorage: Checking for table:
SQOOP_SESSIONS
12/05/08 13:07:02 DEBUG hsqldb.HsqldbJobStorage: Found table: SQOOP_SESSIONS
12/05/08 13:07:02 DEBUG hsqldb.HsqldbJobStorage: Restoring job: zzzzzzzz
12/05/08 13:07:02 DEBUG hsqldb.HsqldbJobStorage: Job: witt_song; Getting
properties with class schema
12/05/08 13:07:02 DEBUG hsqldb.HsqldbJobStorage: Job: witt_song; Getting
properties with class SqoopOptions
12/05/08 13:07:02 DEBUG hsqldb.HsqldbJobStorage: Job: witt_song; Getting
properties with class config
12/05/08 13:07:02 DEBUG hsqldb.HsqldbJobStorage: System property set: 0
12/05/08 13:07:02 DEBUG hsqldb.HsqldbJobStorage: Stored property set: 0
12/05/08 13:07:02 DEBUG sqoop.ConnFactory: Loaded manager factory:
com.cloudera.sqoop.manager.DefaultManagerFactory
12/05/08 13:07:02 DEBUG sqoop.ConnFactory: Trying ManagerFactory:
com.cloudera.sqoop.manager.DefaultManagerFactory
12/05/08 13:07:02 INFO manager.SqlManager: Using default fetchSize of 1000
12/05/08 13:07:02 DEBUG sqoop.ConnFactory: Instantiated ConnManager
com.cloudera.sqoop.manager.GenericJdbcManager@5

Hi Chaitanya,

It appears that one of the methods that Sqoop relies on -
setSessionTimeZone was introduced in Oracle 9i onwards. This makes the
built in connector for Oracle unlikely to work with prior versions of the
database.

If you would like to create a connector to support Oracle 8, we will be
happy to guide you through the steps necessary to get that committed. The
first step would be to open a feature Jira and add your findings to it.

Thanks,
Arvind Prabhakar

On Tue, May 8, 2012 at 11:16 AM, Chaitanya Kocharlakota <
[EMAIL PROTECTED]> wrote:

> Hi,
>
> I am trying to connect Sqoop (Sqoop 1.3.0-cdh3u3) to Oracle 8 using the
> jar file (classes12.jar placed in /usr/lib/sqoop/lib folder). I am not able
> to retrieve the data from the table as it gives me the error "Unable to set
> the Timezone". Here is more info:
>
> Command1: --> Could not set Timezone error
> *sqoop list-tables --connect jdbc:oracle:thin:@nn.nn.nn.nn:1521/XXXXXX
> --username xxxxxxx --password zzzzzzzzz --verbose*
> 12/05/08 12:55:49 DEBUG tool.BaseSqoopTool: Enabled debug logging.
> 12/05/08 12:55:49 WARN tool.BaseSqoopTool: Setting your password on the
> command-line is insecure. Consider using -P instead.
> 12/05/08 12:55:49 DEBUG sqoop.ConnFactory: Loaded manager factory:
> com.cloudera.sqoop.manager.DefaultManagerFactory
> 12/05/08 12:55:49 DEBUG sqoop.ConnFactory: Trying ManagerFactory:
> com.cloudera.sqoop.manager.DefaultManagerFactory
> 12/05/08 12:55:49 DEBUG manager.DefaultManagerFactory: Trying with scheme:
> jdbc:oracle:thin:@nn.nn.nn.nn
> 12/05/08 12:55:49 DEBUG manager.OracleManager$ConnCache: Instantiated new
> connection cache.
> 12/05/08 12:55:49 INFO manager.SqlManager: Using default fetchSize of 1000
> 12/05/08 12:55:49 DEBUG sqoop.ConnFactory: Instantiated ConnManager
> com.cloudera.sqoop.manager.OracleManager@70cb6009
> 12/05/08 12:55:49 DEBUG manager.OracleManager: Creating a new connection
> for jdbc:oracle:thin:@nn.nn.nn.nn:1521/XXXXXX, using username: xxxxxxx
> 12/05/08 12:55:49 DEBUG manager.OracleManager: No connection paramenters
> specified. Using regular API for making connection.
> 12/05/08 12:55:50 WARN manager.OracleManager: Time zone GMT could not be
> set on Oracle database.
> 12/05/08 12:55:50 INFO manager.OracleManager: Setting default time zone:
> GMT
> 12/05/08 12:55:50 ERROR manager.OracleManager: Could not set time zone for
> oracle connection
> java.lang.reflect.InvocationTargetException
>         at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
>         at
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
>         at
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
>         at java.lang.reflect.Method.invoke(Method.java:597)
>         at
> com.cloudera.sqoop.manager.OracleManager.setSessionTimeZone(OracleManager.java:365)
>         at
> com.cloudera.sqoop.manager.OracleManager.makeConnection(OracleManager.java:321)
>         at
> com.cloudera.sqoop.manager.GenericJdbcManager.getConnection(GenericJdbcManager.java:53)
>         at
> com.cloudera.sqoop.manager.OracleManager.listTables(OracleManager.java:688)
>         at
> com.cloudera.sqoop.tool.ListTablesTool.run(ListTablesTool.java:51)
>         at com.cloudera.sqoop.Sqoop.run(Sqoop.java:146)
>         at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:65)
>         at com.cloudera.sqoop.Sqoop.runSqoop(Sqoop.java:182)
>         at com.cloudera.sqoop.Sqoop.runTool(Sqoop.java:221)
>         at com.cloudera.sqoop.Sqoop.runTool(Sqoop.java:230)
>         at com.cloudera.sqoop.Sqoop.main(Sqoop.java:239)
> Caused by: java.sql.SQLException: ORA-01874: time zone hour must be
> between -12 and 13
>
> Command2:  --> Successfully lists tables
> *sqoop list-tables --driver oracle.jdbc.OracleDriver **--connect
> jdbc:oracle:thin:@nn.nn.nn.nn:1521/XXXXXX --username xxxxxxx --password
> zzzzzzzzz --verbose*
> 12/05/08 13:03:37 DEBUG tool.BaseSqoopTool: Enabled debug logging.
> 12/05/08 13:03:37 WARN tool.BaseSqoopTool: Setting your password on the