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 > 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@518bf072 > 12/05/08 13:07:02 INFO tool.CodeGenTool: Beginning code generation > 12/05/08 13:07:02 DEBUG manager.SqlManager: No connection paramenters > specified. Using regular API for making connection. > 12/05/08 13:07:03 DEBUG manager.SqlManager: Using fetchSize for next > query: 1000 > 12/05/08 13:07:03 INFO manager.SqlManager: Executing SQL statement: *SELECT > t.* FROM AIRPLAY AS t WHERE 1=0* > 12/05/08 13:07:03 ERROR manager.SqlManager: Error executing statement: > java.sql.SQLException: ORA-00933: SQL command not properly ended > > java.sql.SQLException: ORA-00933: SQL command not properly ended > at > oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112) > at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:330) > at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:287) > at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:753) > at > oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:218) > at > oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:812) > at > oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1050) > at > oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:853) > at > oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1155) > at > oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3414) > at > oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3459) > at > com.cloudera.sqoop.manager.SqlManager.execute(SqlManager.java:487) > at > com.cloudera.sqoop.manager.SqlManager.execute(SqlManager.java:496) > at > com.cloudera.sqoop.manager.SqlManager.getColumnTypesForRawQuery(SqlManager.java:195) > at > com.cloudera.sqoop.manager.SqlManager.getColumnTypes(SqlManager.java:179) > at > com.cloudera.sqoop.manager.ConnManager.getColumnTypes(ConnManager.java:116) > at > com.cloudera.sqoop.orm.ClassWriter.getColumnTypes(ClassWriter.java:1226) > at > com.cloudera.sqoop.orm.ClassWriter.generate(ClassWriter.java:1051) > at > com.cloudera.sqoop.tool.CodeGenTool.generateORM(CodeGenTool.java:84) > at > com.cloudera.sqoop.tool.ImportTool.importTable(ImportTool.java:370) > at com.cloudera.sqoop.tool.ImportTool.run(ImportTool.java:456) > at com.cloudera.sqoop.tool.JobTool.execJob(JobTool.java:233) > at com.cloudera.sqoop.tool.JobTool.run(JobTool.java:288) > 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) > > It looks like overriding the driver is not the right way of doing it. But > unless, I override the driver, it is not able to go past the timezone error > and after looking at the code, it looks like it is executing "SELECT > CURRENT_TIMESTAMP" in Oracle which does not work in Oracle 8. > > Please let me know how to handle this issue. > > Thanks, > Chaitanya >
