Home | About | Sematext search-lucene.com search-hadoop.com
 Search Hadoop and all its subprojects:

Switch to Threaded View
Sqoop >> mail # user >> FW: Unable to connect to oracle database from sqoop


Copy link to this message
-
RE: Unable to connect to oracle database from sqoop
Hi,
Thanks for your input. It works now. Can you please provide me the list table command to see the list of tables. i tried the below but not working. ./sqoop-list-tables --connect jdbc:oracle:thin:@MyIPAddress:1521/cp.TNSNAME --username scott --password tigerhow to mention the schema name here?

Thanks,
Manickam P

Date: Fri, 21 Jun 2013 11:13:01 +0200
Subject: Re: Unable to connect to oracle database from sqoop
From: [EMAIL PROTECTED]
To: [EMAIL PROTECTED]

Hi.
Use complete table name together with schema in upper case (ie. SCOTT.EMPLOYEE). Also, specify column names in upper case, if you use the --column parameter.

Michal
2013/6/21 Manickam P <[EMAIL PROTECTED]>
Hi Michal,
Thanks for your help. now i'm able to connect with this. when i try to import i'm getting error again.
13/06/21 03:43:15 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.
13/06/21 03:43:15 INFO manager.SqlManager: Using default fetchSize of 100013/06/21 03:43:15 INFO tool.CodeGenTool: Beginning code generation13/06/21 03:43:17 INFO manager.OracleManager: Time zone has been set to GMT
13/06/21 03:43:17 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM EMPLOYEE t WHERE 1=013/06/21 03:43:17 ERROR tool.ImportTool: Imported Failed: Attempted to generate class with no columns!

My script to import table is ./sqoop-import --connect jdbc:oracle:thin:@MyIPAddress:1521/cp.MYTNSNAME --username scott --password tiger --table=EMPLOYEE
Thanks,
Manickam P
Date: Fri, 21 Jun 2013 09:45:22 +0200
Subject: Re: Unable to connect to oracle database from sqoop

From: [EMAIL PROTECTED]
To: [EMAIL PROTECTED]

OK, so either use this connection parameter:
--connect  "jdbc:oracle:thin:scott/tiger@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=MyIPAddress)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=CP.MYTNSNAME)))"
or try this:

--connect jdbc:oracle:thin:@localhost:1521/CP.MYTNSNAME --username scott --password tiger
Michal Taborsky
2013/6/21 Manickam P <[EMAIL PROTECTED]>

Hi Michal,
Here i have provided the details available in my TNSNames.ora file.

MYTNSNAME=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL = TCP)(HOST = MyIPAddress)(PORT = 1521)))(CONNECT_DATA=(SERVICE_NAME = CP.MYTNSNAME)))

Thanks,
Manickam P
Date: Fri, 21 Jun 2013 09:29:30 +0200
Subject: Re: Unable to connect to oracle database from sqoop
From: [EMAIL PROTECTED]
To: [EMAIL PROTECTED]

Well, it looks that you indeed do not have a service "cp" running on your listener (service is something different than SID, is something different than database name and is something different than instance name). Does your connection work from sqlplus for example? If you have the contents of the tnsnames.ora, just paste the whole connection string from there (just compact it so it is on one line). Or post the contents of the tnsnames.ora file here and I can give you some more guidance.
Michal

2013/6/21 Manickam P <[EMAIL PROTECTED]>
Hello Michal,
Thanks for your reply. I tried with your input but after that also i'm getting the same error. i've given below the script i used.
./sqoop-list-databases --connect  "jdbc:oracle:thin:username/password@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=MyIPAddress)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=cp)))"

Do i need to add any jar file in the sqoop lib apart from ojdbc jar?
when i try the above script i got the below exception. I've added the complete stack trace.
13/06/21 02:52:43 INFO manager.SqlManager: Using default fetchSize of 100013/06/21 02:52:43 ERROR manager.OracleManager: Failed to rollback transaction
java.lang.NullPointerException        at org.apache.sqoop.manager.OracleManager.listDatabases(OracleManager.java:615)        at org.apache.sqoop.tool.ListDatabasesTool.run(ListDatabasesTool.java:49)
        at org.apache.sqoop.Sqoop.run(Sqoop.java:145)        at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:65)        at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:181)        at org.apache.sqoop.Sqoop.runTool(Sqoop.java:220)
        at org.apache.sqoop.Sqoop.runTool(Sqoop.java:229)        at org.apache.sqoop.Sqoop.main(Sqoop.java:238)13/06/21 02:52:43 ERROR manager.OracleManager: Failed to list databases
java.sql.SQLException: Listener refused the connection with the following error:ORA-12514, TNS:listener does not currently know of service requested in connect descriptor        at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:458)
        at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:546)        at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:236)        at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:32)
        at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:521)        at java.sql.DriverManager.getConnection(Unknown Source)        at java.sql.DriverManager.getConnection(Unknown Source)
        at org.apache.sqoop.manager.OracleManager.makeConnection(OracleManager.java:308)        at org.apache.sqoop.manager.GenericJdbcManager.getConnection(GenericJdbcManager.java:52)        at org.apache.sqoop.manager.OracleManager.listDatabases(OracleManager.java:604)
        at org.apache.sqoop.tool.ListDatabasesTool.run(ListDatabasesTool.java:49)        at org.apache.sqoop.Sqoop.run(Sqoop.java:145)        at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:65)
        at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:181)        at org.apache.sqoop.Sqoop.runTool(Sqoop.java:220)        at org.apache.sqoop.Sqoop.runTool(Sqoop.java:229)        at org.apache.sqoop.Sqoop.main(Sqoop.java:238)
Caused by: oracle.net.ns.NetException: Listener refused the connection with the following error:ORA-12514, TNS:listener does not currently know of service requested in connect descriptor

        at oracle.net.ns.NSProtocol.connect(NSProtocol.java:395)