Thread: [Squirrel-sql-users] java.sql.SQLException: ORA-12154: TNS:could not resolve the connect identifier
A Java SQL client for any JDBC compliant database
Brought to you by:
colbell,
gerdwagner
From: László M. <la...@mo...> - 2009-08-27 15:46:17
|
Hi guys, export ORACLE_BASE=/usr/lib/oracle/xe/app/oracle export ORACLE_HOME=/usr/lib/oracle/xe/app/oracle/product/10.2.0/server export ORACLE_SID=XE export TNS_ADMIN=/usr/lib/oracle export LD_LIBRARY_PATH=/usr/lib/oracle/xe/app/oracle/product/10.2.0/server/lib After setting the above environment variables I can access Oracle through the sqlplus utility, but SQuirrel says the following: ----8<---- oracle: ORA-12154: TNS:could not resolve the connect identifier specified java.sql.SQLException: ORA-12154: TNS:could not resolve the connect identifier specified at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112) at oracle.jdbc.driver.T2CConnection.checkError(T2CConnection.java:672) at oracle.jdbc.driver.T2CConnection.logon(T2CConnection.java:346) at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:414) at oracle.jdbc.driver.T2CConnection.<init>(T2CConnection.java:132) at oracle.jdbc.driver.T2CDriverExtension.getConnection(T2CDriverExtension.java:78) at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:801) at net.sourceforge.squirrel_sql.fw.sql.SQLDriverManager.getConnection(SQLDriverManager.java:133) at net.sourceforge.squirrel_sql.client.mainframe.action.OpenConnectionCommand.execute(OpenConnectionCommand.java:97) at net.sourceforge.squirrel_sql.client.mainframe.action.ConnectToAliasCommand$SheetHandler.run(ConnectToAliasCommand.java:280) at net.sourceforge.squirrel_sql.fw.util.TaskExecuter.run(TaskExecuter.java:82) at java.lang.Thread.run(Thread.java:619) ----8<---- What could be the problem? Thanks in advance! -- Laci <http://monda.hu> |
From: Andreas O. <cpt...@gm...> - 2009-08-27 16:01:50
|
Hi László, from the environment varibles I'd guess your about to connect an Oracle Database Express Edition 10.2. So if you used standard installation the ORACLE_SID for this installation should be "XE". Where is the oracle server running? What is the hostname of the machine? What JDBC-connect-string did you use to connect to the database? If you run Oracle XE on the same machine as Squirrel then the connect string should be something like "jdbc:oracle:thin:@localhost:1521:xe" Where "localhost" may need to be replaced with the hostname of the oracle-server, 1521 is the standard-port for an oracle server and "xe" is the SID. Perhaps you post the output of "tnsping xe" Did you configure the "Oracle Thin Driver" in SQuirreL first? The exception looks as if it comes from Oracle JDBC driver, so your probably have installed the driver correctly. If not, you should download the latest JDBC-driver from Oracle and put it in the squirrel lib folder. Hope that helps, if you need further help, do not hesitate to contact me again Good luck Andreas László Monda schrieb: > Hi guys, > > export ORACLE_BASE=/usr/lib/oracle/xe/app/oracle > export ORACLE_HOME=/usr/lib/oracle/xe/app/oracle/product/10.2.0/server > export ORACLE_SID=XE > export TNS_ADMIN=/usr/lib/oracle > export LD_LIBRARY_PATH=/usr/lib/oracle/xe/app/oracle/product/10.2.0/server/lib > > After setting the above environment variables I can access Oracle > through the sqlplus utility, but SQuirrel says the following: > > ----8<---- > > oracle: ORA-12154: TNS:could not resolve the connect identifier specified > > java.sql.SQLException: ORA-12154: TNS:could not resolve the connect > identifier specified > > at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112) > at oracle.jdbc.driver.T2CConnection.checkError(T2CConnection.java:672) > at oracle.jdbc.driver.T2CConnection.logon(T2CConnection.java:346) > at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:414) > at oracle.jdbc.driver.T2CConnection.<init>(T2CConnection.java:132) > at oracle.jdbc.driver.T2CDriverExtension.getConnection(T2CDriverExtension.java:78) > at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:801) > at net.sourceforge.squirrel_sql.fw.sql.SQLDriverManager.getConnection(SQLDriverManager.java:133) > at net.sourceforge.squirrel_sql.client.mainframe.action.OpenConnectionCommand.execute(OpenConnectionCommand.java:97) > at net.sourceforge.squirrel_sql.client.mainframe.action.ConnectToAliasCommand$SheetHandler.run(ConnectToAliasCommand.java:280) > at net.sourceforge.squirrel_sql.fw.util.TaskExecuter.run(TaskExecuter.java:82) > at java.lang.Thread.run(Thread.java:619) > > ----8<---- > > What could be the problem? > > Thanks in advance! > > |
From: László M. <la...@mo...> - 2009-08-27 16:28:17
|
Hi Andreas, On Thu, Aug 27, 2009 at 6:01 PM, Andreas Oswald<cpt...@gm...> wrote: > Hi László, > > from the environment varibles I'd guess your about to connect an Oracle > Database Express Edition 10.2. Yes, exactly. Oracle Express Edition 10g, version 10.2.0.1 from the Ubuntu Intrepid repository to be exact. > So if you used standard installation the ORACLE_SID for this installation > should be "XE". Where is the oracle server running? On localhost. > What is the hostname of the machine? whisper > What JDBC-connect-string did you use to connect to the database? > > If you run Oracle XE on the same machine as Squirrel then the connect string > should be something like "jdbc:oracle:thin:@localhost:1521:xe" > > Where "localhost" may need to be replaced with the hostname of the > oracle-server, 1521 is the standard-port for an oracle server and "xe" is > the SID. I did not specify any JDBC connect strings, since I can only edit the User and Password fields in the "Connection to: oracle" window. I'm pretty damn interested how can I connect to external hosts and edit the connection string. > Perhaps you post the output of "tnsping xe" laci@whisper:~/apps/squirrel$ tnsping xe TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 27-AUG-2009 18:23:31 Copyright (c) 1997, 2005, Oracle. All rights reserved. Used parameter files: Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = whisper)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XE))) OK (0 msec) > Did you configure the "Oracle Thin Driver" in SQuirreL first? I did not look into that yet, but I will if the OCI driver won't work. > The exception > looks as if it comes from Oracle JDBC driver, so your probably have > installed the driver correctly. > If not, you should download the latest JDBC-driver from Oracle and put it in > the squirrel lib folder. I've already installed the latest JDBC driver from Oracle for the specific DB version that I use. > Hope that helps, if you need further help, do not hesitate to contact me > again > > Good luck Thanks a lot! > Andreas > > > László Monda schrieb: >> >> Hi guys, >> >> export ORACLE_BASE=/usr/lib/oracle/xe/app/oracle >> export ORACLE_HOME=/usr/lib/oracle/xe/app/oracle/product/10.2.0/server >> export ORACLE_SID=XE >> export TNS_ADMIN=/usr/lib/oracle >> export >> LD_LIBRARY_PATH=/usr/lib/oracle/xe/app/oracle/product/10.2.0/server/lib >> >> After setting the above environment variables I can access Oracle >> through the sqlplus utility, but SQuirrel says the following: >> >> ----8<---- >> >> oracle: ORA-12154: TNS:could not resolve the connect identifier specified >> >> java.sql.SQLException: ORA-12154: TNS:could not resolve the connect >> identifier specified >> >> at >> oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112) >> at >> oracle.jdbc.driver.T2CConnection.checkError(T2CConnection.java:672) >> at oracle.jdbc.driver.T2CConnection.logon(T2CConnection.java:346) >> at >> oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:414) >> at oracle.jdbc.driver.T2CConnection.<init>(T2CConnection.java:132) >> at >> oracle.jdbc.driver.T2CDriverExtension.getConnection(T2CDriverExtension.java:78) >> at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:801) >> at >> net.sourceforge.squirrel_sql.fw.sql.SQLDriverManager.getConnection(SQLDriverManager.java:133) >> at >> net.sourceforge.squirrel_sql.client.mainframe.action.OpenConnectionCommand.execute(OpenConnectionCommand.java:97) >> at >> net.sourceforge.squirrel_sql.client.mainframe.action.ConnectToAliasCommand$SheetHandler.run(ConnectToAliasCommand.java:280) >> at >> net.sourceforge.squirrel_sql.fw.util.TaskExecuter.run(TaskExecuter.java:82) >> at java.lang.Thread.run(Thread.java:619) >> >> ----8<---- >> >> What could be the problem? >> >> Thanks in advance! >> >> > > -- Laci <http://monda.hu> |
From: John H. <jh...@im...> - 2009-08-27 16:46:49
|
On Thu, 27 Aug 2009, László Monda wrote: >> What JDBC-connect-string did you use to connect to the database? > > I did not specify any JDBC connect strings, since I can only edit the > User and Password fields in the "Connection to: oracle" window. > > I'm pretty damn interested how can I connect to external hosts and > edit the connection string. >>> export ORACLE_SID=XE WAG: If you use the OIC driver instead of the Thin driver, you may need to specify the connection information (host, port, etc.) for that SID in the tnsnames.ora file rather than in a JDBC connection string. -- John Hardin KA7OHZ http://www.impsec.org/~jhardin/ jh...@im... FALaholic #11174 pgpk -a jh...@im... key: 0xB8732E79 -- 2D8C 34F4 6411 F507 136C AF76 D822 E6E6 B873 2E79 ----------------------------------------------------------------------- Politicians never accuse you of "greed" for wanting other people's money, only for wanting to keep your own money. -- Joseph Sobran ----------------------------------------------------------------------- Tomorrow: Exercise Your Rights day |
From: László M. <la...@mo...> - 2009-08-27 16:51:28
|
On Thu, Aug 27, 2009 at 6:46 PM, John Hardin<jh...@im...> wrote: > On Thu, 27 Aug 2009, László Monda wrote: > >>> What JDBC-connect-string did you use to connect to the database? >> >> I did not specify any JDBC connect strings, since I can only edit the >> User and Password fields in the "Connection to: oracle" window. >> >> I'm pretty damn interested how can I connect to external hosts and >> edit the connection string. > >>>> export ORACLE_SID=XE > > WAG: If you use the OIC driver instead of the Thin driver, you may need to > specify the connection information (host, port, etc.) for that SID in the > tnsnames.ora file rather than in a JDBC connection string. I'm sure tnsnames.ora contains correct informations because I can easily login using the sqlplus utility. > > -- > John Hardin KA7OHZ http://www.impsec.org/~jhardin/ > jh...@im... FALaholic #11174 pgpk -a jh...@im... > key: 0xB8732E79 -- 2D8C 34F4 6411 F507 136C AF76 D822 E6E6 B873 2E79 > ----------------------------------------------------------------------- > Politicians never accuse you of "greed" for wanting other people's > money, only for wanting to keep your own money. -- Joseph Sobran > ----------------------------------------------------------------------- > Tomorrow: Exercise Your Rights day -- Laci <http://monda.hu> |
From: Andreas O. <cpt...@gm...> - 2009-08-27 17:06:14
|
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> <html> <head> <meta content="text/html;charset=UTF-8" http-equiv="Content-Type"> </head> <body bgcolor="#ffffff" text="#000000"> Hi László, <br> <br> László Monda schrieb: <blockquote cite="mid:4d5...@ma..." type="cite"> <pre wrap="">I did not specify any JDBC connect strings, since I can only edit the User and Password fields in the "Connection to: oracle" window. </pre> </blockquote> <pre wrap="">So you did not setup an alias in SQuirreL for this connection? Actually I don't know what you mean with "Connection to: oracle" window. What kind of window do you see with which entries when you choose "Window/Show Drivers" in the main menu (not quite sure about the correct english title, since I use a localized version of SQuirreL)</pre> <blockquote cite="mid:4d5...@ma..." type="cite"> <pre wrap=""></pre> <blockquote type="cite"> <pre wrap="">Perhaps you post the output of "tnsping xe" </pre> </blockquote> <pre wrap=""><!----> laci@whisper:~/apps/squirrel$ tnsping xe TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 27-AUG-2009 18:23:31 Copyright (c) 1997, 2005, Oracle. All rights reserved. Used parameter files: Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = whisper)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XE))) OK (0 msec) </pre> </blockquote> OK, your JDBC connect string should be something like "jdbc:oracle:thin:@whisper:1521:xe if you use the Oracle Thin Driver <br> <br> You should be able to test that when you create a new alias and copy the connect string to the "URL" field after you've chosen one of the available oracle drivers. <br> <br> I've just done some testing with the OCI-driver from jdbc-folder in my ORACLE_HOME and I got it up and running to with the URL "jdbc:oracle:oci8:@xe" A plus of using the thin driver is that you don't have to install the whole Oracle Client on your machine. But that is not important when you run SQuirreL and Oracle on the same machine, as you have the Oracle Client installed already with the server installation. I could use "XE" too, as I have an entry "XE" in my tnsnames.ora pointing to my local XE Server. <br> <br> For connecting to other machines you have to extend your tnsnames.ora file and add further entries there.<br> <br> For example if you had another machine running Oracle XE as well and this machine was called "test" you could add the following to your tnsnames.ora file<br> <br> TEST =<br> (DESCRIPTION =<br> (ADDRESS = (PROTOCOL = TCP)(HOST = TEST)(PORT = 1521))<br> (CONNECT_DATA =<br> (SERVER = DEDICATED)<br> (SERVICE_NAME = XE)<br> )<br> )<br> <br> After that you should be able to do "tnsping test" or do "sqlplus system@test" to connect to the Oracle on TEST with the system account<br> <br> When wanting to connect with the OCI-driver you would nee to use "jdbc:oracle:oci8:@test" to connect to the Oracle server on TEST<br> <br> If you use the Thin-Driver, then you just need the Hostname in the JDBC-connect string and don't have to take care of tnsnames.ora but extending that file will help you, if you want to use oracle client tools as well. <br> <br> Hope that helps<br> <br> Have a nice evening and take care<br> <br> Andreas<br> <br> </body> </html> |
From: László M. <la...@mo...> - 2009-08-31 12:43:17
|
Hi Andreas, Sorry for the late reply and thanks for the throrough help. I've solved the issue in the meantime. By the "Connection to: oracle" window I meant the window that appears when selecting the "Connect..." menu item from the popup menu of my "oracle" connection alias. I invoked this window by double clicking on this connection alias. I assumed that editing the JDBC connection string should be possible from this window, but only later I realized that in order to specify a JDBC connection one has to create a new alias. Using the Oracle Thin Driver I can connect to Oracle an in a much more easier way than using the OCI driver without editing any config files. Thanks for the help and take care! On Thu, Aug 27, 2009 at 7:05 PM, Andreas Oswald<cpt...@gm...> wrote: > Hi László, > > László Monda schrieb: > > I did not specify any JDBC connect strings, since I can only edit the > User and Password fields in the "Connection to: oracle" window. > > > So you did not setup an alias in SQuirreL for this connection? Actually I > don't know what you mean with "Connection to: oracle" window. What kind of > window do you see with which entries when you choose "Window/Show Drivers" > in the main menu (not quite sure about the correct english title, since I > use a localized version of SQuirreL) > > Perhaps you post the output of "tnsping xe" > > > laci@whisper:~/apps/squirrel$ tnsping xe > > TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on > 27-AUG-2009 18:23:31 > > Copyright (c) 1997, 2005, Oracle. All rights reserved. > > Used parameter files: > > > Used TNSNAMES adapter to resolve the alias > Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST > = whisper)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) > (SERVICE_NAME = XE))) > OK (0 msec) > > > > OK, your JDBC connect string should be something like > "jdbc:oracle:thin:@whisper:1521:xe if you use the Oracle Thin Driver > > You should be able to test that when you create a new alias and copy the > connect string to the "URL" field after you've chosen one of the available > oracle drivers. > > I've just done some testing with the OCI-driver from jdbc-folder in my > ORACLE_HOME and I got it up and running to with the URL > "jdbc:oracle:oci8:@xe" A plus of using the thin driver is that you don't > have to install the whole Oracle Client on your machine. But that is not > important when you run SQuirreL and Oracle on the same machine, as you have > the Oracle Client installed already with the server installation. I could > use "XE" too, as I have an entry "XE" in my tnsnames.ora pointing to my > local XE Server. > > For connecting to other machines you have to extend your tnsnames.ora file > and add further entries there. > > For example if you had another machine running Oracle XE as well and this > machine was called "test" you could add the following to your tnsnames.ora > file > > TEST = > (DESCRIPTION = > (ADDRESS = (PROTOCOL = TCP)(HOST = TEST)(PORT = 1521)) > (CONNECT_DATA = > (SERVER = DEDICATED) > (SERVICE_NAME = XE) > ) > ) > > After that you should be able to do "tnsping test" or do "sqlplus > system@test" to connect to the Oracle on TEST with the system account > > When wanting to connect with the OCI-driver you would nee to use > "jdbc:oracle:oci8:@test" to connect to the Oracle server on TEST > > If you use the Thin-Driver, then you just need the Hostname in the > JDBC-connect string and don't have to take care of tnsnames.ora but > extending that file will help you, if you want to use oracle client tools as > well. > > Hope that helps > > Have a nice evening and take care > > Andreas > > -- Laci <http://monda.hu> |