[SQOOP-1959] sqoop failed to import data from oracle whether specify the driver option or not

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Done
• Affects Version/s: 1.4.5
• Component/s: connectors/oracle

Environment

CentOS release 5.5
sqoop : 1.4.5
hive: 0.11.0
hadoop: 1.2.1
jdk: 1.7.0_71
oracle: 10.0.1.0
oracle jdbc driver: ojdbc6.jar

• Assignee: Unassigned
• Reporter: daniel
• Created: 26/Dec/14 13:53
• Updated: 19/Dec/15 08:04
• Resolved: 28/Feb/15 03:46

Description

The ojdbc6.jar works fine through the PLSQL client. While trying to import data from oracle, the sqoop import didn't work whether specifying the -driver option or not.

(1) use the default driver (oracle.jdbc.OracleDriver ),

a) can't get tables.
sqoop list-tables --connect jdbc:oracle:thin:@IP:HOST:DB01 --username XX --password XX

14/12/26 05:04:32 INFO sqoop.Sqoop: Running Sqoop version: 1.4.5
14/12/26 05:04:32 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.
14/12/26 05:04:32 INFO oracle.OraOopManagerFactory: Data Connector for Oracle and Hadoop is disabled.
14/12/26 05:04:32 INFO manager.SqlManager: Using default fetchSize of 1000
14/12/26 05:04:33 INFO manager.OracleManager: Time zone has been set to GMT

b)
ERROR tool.ImportTool: Imported Failed: There is no column found in the target table TABLE01. Please ensure that your table name is correct.
sqoop import --driver oracle.jdbc.driver.OracleDriver --hive-import --connect jdbc:oracle:thin:@IP:HOST:SID --username XX --password XX --verbose -m 1 --table TABLE01

......
14/12/26 05:36:55 DEBUG sqoop.ConnFactory: Instantiated ConnManager org.apache.sqoop.manager.OracleManager@b15b5a
14/12/26 05:36:55 INFO tool.CodeGenTool: Beginning code generation
14/12/26 05:36:55 DEBUG manager.OracleManager: Using column names query: SELECT t.* FROM TABLE01 t WHERE 1=0
14/12/26 05:36:55 DEBUG manager.SqlManager: Execute getColumnInfoRawQuery : SELECT t.* FROM TABLE01 t WHERE 1=0
14/12/26 05:36:56 DEBUG manager.OracleManager: Creating a new connection for jdbc:oracle:thin:@IP:HOST:SID, using username: XX
14/12/26 05:36:56 DEBUG manager.OracleManager: No connection paramenters specified. Using regular API for making connection.
14/12/26 05:36:56 INFO manager.OracleManager: Time zone has been set to GMT
14/12/26 05:36:56 DEBUG manager.SqlManager: Using fetchSize for next query: 1000
14/12/26 05:36:56 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM TABLE01 t WHERE 1=0
{color:red}
14/12/26 05:36:56 DEBUG manager.OracleManager$ConnCache: Caching released connection for jdbc:oracle:thin:@IP:HOST:SID/XX
14/12/26 05:36:56 ERROR tool.ImportTool: Imported Failed: There is no column found in the target table TABLE01. Please ensure that your table name is correct.
{color}

(2) specify the "--driver " option: --driver oracle.jdbc.driver.OracleDriver

a) correctly list the tables of DB01
sqoop list-tables --driver oracle.jdbc.driver.OracleDriver --connect jdbc:oracle:thin:@IP:HOST:SID --username XX --password XX

14/12/26 05:32:13 INFO manager.SqlManager: Using default fetchSize of 1000
WWV_FLOW_DUAL100
WWV_FLOW_LOV_TEMP
WWV_FLOW_TEMP_TABLE
TABLE01
......

b) same as issues https://issues.apache.org/jira/browse/SQOOP-457, ORA-00933: SQL command not properly ended
sqoop import --driver oracle.jdbc.driver.OracleDriver --hive-import --connect jdbc:oracle:thin:@IP:HOST:SID --username XX --password XX --verbose -m 1 --table TABLE01

14/12/26 05:33:49 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM TABLE01 AS t WHERE 1=0
14/12/26 05:33:49 ERROR manager.SqlManager: Error executing statement: java.sql.SQLSyntaxErrorException: ORA-00933: SQL command not properly ended
java.sql.SQLSyntaxErrorException: ORA-00933: SQL command not properly ended
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:439)
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:395)
	at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:802)
	......