You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@sqoop.apache.org by "Michael Shaw (JIRA)" <ji...@apache.org> on 2015/09/01 00:35:46 UTC
[jira] [Commented] (SQOOP-1959) sqoop failed to import data from
oracle whether specify the driver option or not
[ https://issues.apache.org/jira/browse/SQOOP-1959?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14724157#comment-14724157 ]
Michael Shaw commented on SQOOP-1959:
-------------------------------------
I was experiencing this error as well.
Here is what worked for me:
1.) Do not specify Oracle driver
2.) Specify table name all uppercase
> sqoop failed to import data from oracle whether specify the driver option or not
> --------------------------------------------------------------------------------
>
> Key: SQOOP-1959
> URL: https://issues.apache.org/jira/browse/SQOOP-1959
> Project: Sqoop
> Issue Type: Bug
> Components: connectors/oracle
> Affects Versions: 1.4.5
> 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
> Reporter: daniel
> Original Estimate: 72h
> Remaining Estimate: 72h
>
> 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.
> h3. (1) use the default driver (oracle.jdbc.OracleDriver ),
> a) {color:red}can't get tables.{color}
> *sqoop list-tables --connect jdbc:oracle:thin:@IP:HOST:DB01 --username XX --password XX*
> {noformat}
> 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
> {noformat}
> b) {color:red}
> ERROR tool.ImportTool: Imported Failed: There is no column found in the target table TABLE01. Please ensure that your table name is correct.
> {color}
> *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*
> {noformat}
> ......
> 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}
> {noformat}
> h3. (2) specify the "--driver " option: --driver oracle.jdbc.driver.OracleDriver
> a) {color:red}correctly list the tables of DB01{color}
> *sqoop list-tables --driver oracle.jdbc.driver.OracleDriver --connect jdbc:oracle:thin:@IP:HOST:SID --username XX --password XX*
> {noformat}
> 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
> ......
> {noformat}
> b) {color:red}same as issues [https://issues.apache.org/jira/browse/SQOOP-457], ORA-00933: SQL command not properly ended{color}
> *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*
> {noformat}
> 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)
> ......
> {noformat}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)