Usernames should be in upper case as mentioned above. With list-all-tables, you get the list of tables owned by the user, which is SCOTT in this case
Thanks Venkat On Fri, Jun 21, 2013 at 2:39 AM, Manickam P <[email protected]> wrote: > 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 tiger > > how 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 1000 > 13/06/21 03:43:15 INFO tool.CodeGenTool: Beginning code generation > 13/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=0 > 13/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 1000 > 13/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) > at > oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1102) > at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:320) > ... 16 more > > > > Thanks, > Manickam P > ------------------------------ > Date: Fri, 21 Jun 2013 08:47:11 +0200 > Subject: Re: Unable to connect to oracle database from sqoop > From: [email protected] > To: [email protected] > > > Hello Manickam, > > the Oracle connection parameters are a bit confusing sometimes (not a > Sqoop problem). The thin client, which you are using, does not support full > Oracle*Net client and it does not work with tnasnames.ora. So you either > specify the service directly (in your case, the listener running on > localhost must provide service "cp"). Or you can provide the full > connection string (taken from tnsnames.ora) in the commandline: > > For example, like this: > sqoop import --connect > "jdbc:oracle:thin:username/password@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=YOURSERVICENAME)))" > ... > > Hope this helps, > Michal > > > 2013/6/21 Manickam P <[email protected]> > > Hi, > > Given below is the scoop script i used. > > > ./sqoop list-databases --connect jdbc:oracle:thin:@localhost:1521/cp > --username scott --password tiger > > JDBC driver directory - which directory you mean here? i have put ojdbc > jar in sqoop lib folder. > Is that fine or am i missing anything here? > > > Thanks, > Manickam P > > ------------------------------ > Date: Thu, 20 Jun 2013 16:04:50 +0200 > Subject: Re: FW: Unable to connect to oracle database from sqoop > From: [email protected] > To: [email protected] > > > Hi, > > Please send your Sqoop script, and the output of "ls" command on your JDBC > driver directory. > > Thanks > > > 2013/6/20 Manickam P <[email protected]> > > Hi, > > I am trying to import details from oracle through sqoop but i got the > below error. > 13/06/20 09:09:21 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 > > I've put an entry into the TNSname.ora file and also added ojdbc jar with > sqoop lib folder. > > Please help me to resolve the issue. > > > Thanks, > Manickam P > > > > > > > > > > > -- Regards Venkat
