Support Questions
Find answers, ask questions, and share your expertise

sqoop connection to oracle causes an exception

Highlighted

sqoop connection to oracle causes an exception

sqoop Connection to oracle is giving following exception :

17/09/15 13:47:09 ERROR sqoop.Sqoop: Got exception running Sqoop: java.lang.NullPointerException
java.lang.NullPointerException
at org.apache.sqoop.manager.OracleManager.listTables(OracleManager.java:769)
at org.apache.sqoop.tool.ListTablesTool.run(ListTablesTool.java:49)
at org.apache.sqoop.Sqoop.run(Sqoop.java:147)
at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:76)
at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:183)
at org.apache.sqoop.Sqoop.runTool(Sqoop.java:225)
at org.apache.sqoop.Sqoop.runTool(Sqoop.java:234)
at org.apache.sqoop.Sqoop.main(Sqoop.java:243)

checked the connectivity between the cluster and Oracle server using telnet <server Name> <port> and it works.

used the same sqoop command from differnt server which is not behind the firewall and it works too.

3 REPLIES 3

Re: sqoop connection to oracle causes an exception

Contributor

@kishan vantakala Can you please paste the full logs using --verbose option while running the command here.

Highlighted

Re: sqoop connection to oracle causes an exception

Super Mentor

@kishan vantakala

The error that you are getting indicates that the Connection is not established with the Oracle Database.

ERROR sqoop.Sqoop: Got exception running Sqoop: java.lang.NullPointerException
java.lang.NullPointerException
    at org.apache.sqoop.manager.OracleManager.listTables(OracleManager.java:769)
    at org.apache.sqoop.tool.ListTablesTool.run(ListTablesTool.java:49)
    at org.apache.sqoop.Sqoop.run(Sqoop.java:147)


Mostly likely it it due to either the Database hostname is not resolvable from your machine where you are running the sqoop client Or the --connect URL is not in correct format (like missing SID/Service name)


You can try doing a telnet to see if you can reach to the database. (jdbc:oracle:thin:@host:1521/Servicename)

# telnet  dbHost  1521
OR
# nc  -v  dbHost  1521

.

Check if you are able to connect to that host & port? If not then try specifying the IP Address instead and then try again.

Example:

# sqoop list-tables --connect "jdbc:oracle:thin:@dbHost:1521:ORCL" --username usr1 -password yourpwd
(OR)
# sqoop list-tables --connect "jdbc:oracle:thin:@dbHost:1521/XE" --username usr1 -password yourpwd

.

Please see similar discussion: https://community.hortonworks.com/questions/92437/sqoop-connection-to-oracle-from-sandbox-gives-erro...

Highlighted

Re: sqoop connection to oracle causes an exception

Following is the log with verbose option

$ sqoop list-tables --options-file SqoopConnectParms.txt --verbose
Warning: /usr/hdp/2.6.1.0-129/accumulo does not exist! Accumulo imports will fail.
Please set $ACCUMULO_HOME to the root of your Accumulo installation.
17/09/18 15:37:05 INFO sqoop.Sqoop: Running Sqoop version: 1.4.6.2.6.1.0-129
17/09/18 15:37:05 DEBUG tool.BaseSqoopTool: Enabled debug logging.
17/09/18 15:37:05 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.
17/09/18 15:37:05 DEBUG sqoop.ConnFactory: Loaded manager factory: org.apache.sqoop.manager.oracle.OraOopManagerFactory
17/09/18 15:37:05 DEBUG sqoop.ConnFactory: Loaded manager factory: com.cloudera.sqoop.manager.DefaultManagerFactory
17/09/18 15:37:05 DEBUG sqoop.ConnFactory: Trying ManagerFactory: org.apache.sqoop.manager.oracle.OraOopManagerFactory
17/09/18 15:37:05 DEBUG oracle.OraOopManagerFactory: Data Connector for Oracle and Hadoop can be called by Sqoop!
17/09/18 15:37:05 INFO oracle.OraOopManagerFactory: Data Connector for Oracle and Hadoop is disabled.
17/09/18 15:37:05 DEBUG sqoop.ConnFactory: Trying ManagerFactory: com.cloudera.sqoop.manager.DefaultManagerFactory
17/09/18 15:37:05 DEBUG manager.DefaultManagerFactory: Trying with scheme: jdbc:oracle:thin
17/09/18 15:37:05 DEBUG manager.OracleManager$ConnCache: Instantiated new connection cache.
17/09/18 15:37:05 INFO manager.SqlManager: Using default fetchSize of 1000
17/09/18 15:37:05 DEBUG sqoop.ConnFactory: Instantiated ConnManager org.apache.sqoop.manager.OracleManager@65d09a04
17/09/18 15:37:05 DEBUG manager.OracleManager: Creating a new connection for jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=xxxxxx.xxx.com)(PORT=1523)))(CONNECT_DATA=(SERVICE_NAME=rtd1perf)(SERVER=DEDICATED))))), using username: HADOOPUSER
17/09/18 15:37:05 DEBUG manager.OracleManager: No connection paramenters specified. Using regular API for making connection.
17/09/18 15:38:05 ERROR sqoop.Sqoop: Got exception running Sqoop: java.lang.NullPointerException
java.lang.NullPointerException
at org.apache.sqoop.manager.OracleManager.listTables(OracleManager.java:769)
at org.apache.sqoop.tool.ListTablesTool.run(ListTablesTool.java:49)
at org.apache.sqoop.Sqoop.run(Sqoop.java:147)
at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:76)
at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:183)
at org.apache.sqoop.Sqoop.runTool(Sqoop.java:225)
at org.apache.sqoop.Sqoop.runTool(Sqoop.java:234)
at org.apache.sqoop.Sqoop.main(Sqoop.java:243)