0

We are new to ORACLE. We installed ORACLE 18c XE, along with DCS, SQL*Plus and SQL Developer. This is all done locally (local laptop) just so we can get up to speed. Our long term intention is to simply create a VS Project and use ODP.Net to extract data.

We are right now using the System user with the password created during installation. With this user we can connect within SQL Developer to the default connection "XE" that was creation during Install. This user however cannot connect using SQL Plus. It also will not connect using ODP.Net in our practice Visual Studio Project. Both SQL Plus and ODP.Net give same error: "ORA-12154: TNS: could not resolve the connect idenfification specified." This is the connection string in the VS Project: "Data Source=XE;User Id=system;Password=createdpassword!;"

With ODP.Net in our VS Practice project the following DOES connect: "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=XPS13MDi.home)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=XE)));User Id=system;Password=createdpassword;" where XPS13MDi is our Laptop name. This seems to be identical to what the XE entry is in tnsnames.ora.

We've google a 1000 times, tried everything we found with tnsnames.ora, listener.ora, etc....nothing seems to work. Does not seem like this should be so difficult, but as mentioned we are new to ORACLE. Ideally we would like to user the tnsnames.ora as the connection identifier when our application is rolled out.

Ideas/corrections/etc...would be appreciated.

midnite11
  • 81
  • 1
  • 10
  • 1
    The error you get is because your setup does not include a pointer to where your tnsnames.ora is. Try setting TNS_ADMIN=/path/to/your/tnsnames.ora (directory not file), and try again. If this variable is not set, you have to set ORACLE_HOME and use the tnsnames.ora that is in ORACLE_HOME/network/admin – gsalem Mar 02 '20 at 20:28
  • Have a look at https://stackoverflow.com/questions/28280883/determining-location-of-relevant-tnsnames-ora-file/28283924#28283924 – Wernfried Domscheit Mar 02 '20 at 21:22
  • Thank you. Where is the TNS_ADMIN setting take place? Is this a config file somewhere? – midnite11 Mar 02 '20 at 21:32
  • We have added TNS_ADMIN as an Environment Variable. We then added the path to the tnsnames.ora file (path only). We then try and log into SQL Plus and we still get the same error. – midnite11 Mar 03 '20 at 15:47
  • Another try...we've seen that $ORACLE_HOME should be set to the $ORACLE_HOME/network/admin....in the registry we have this as the string "ORACLE_HOME" key "C:\oracle\zmidn\product\18.0.0\dbhomeXE" and with this setting SQL Plus will open to the "Enter User Name"...if we change the Registry to "C:\oracle\zmidn\product\18.0.0\dbhomeXE\network\admin"...then when selecting SQL Plus the command window does not open at all and in our VS Practice Project none of the connect strings work. Why is this so difficult????? – midnite11 Mar 03 '20 at 16:09
  • From memory sqlplus first looks for a registry entry for TNS_ADMIN then if not set looks at the TNS_ADMIN environment variable.... So couple of things you can try, 1) search your registry and see where TNS_ADMIN is pointing if it is set and ensure that you have this tnsnames.ora set in that location OR 2) just do a search of your entire machine for tnsnames.ora and replace ALL of them with your version, if this works you can narrow down which one SQLplus ect are looking at. – Shaun Peterson Mar 05 '20 at 22:06
  • Thanx for the response. For our install on our local laptop, we know that SQL Plus is looking at the ORACLE_HOME setting in the registry. It is currently set at this: "C:\oracle\zmidn\product\18.0.0\dbhomeXE". With this setting, SQL Plus opens to the first prompt of User Name. This TNS_ADMIN file that is working for ODP.Net in our test Visual Studio Project is located here: "C:\oracle\zmidn\product\18.0.0\dbhomeXE\network\admin". – midnite11 Mar 06 '20 at 20:38
  • If we change the Registry setting of ORACLE_HOME to ANYTHING else - SQL Plus window does not open at all. We do have SQL Developer and we can connect with this. Since our goal is to extract ORACLE data from within our own application using the ODP.net assemblies and we are successfully doing that now - we are slowly getting over the fact that SQL Plus is being tempermental. Its just frustrating that the entire ORACLE install is not working, only pieces. – midnite11 Mar 06 '20 at 20:38
  • We have Environment Variables set for both ORACLE_HOME: "C:\oracle\zmidn\product\18.0.0\dbhomeXE\network\admin" and TNS_ADMIN:"C:\oracle\zmidn\product\18.0.0\dbhomeXE\network\admin". Changing the setting for any of these result in the ODP.net in our Visual Studio Project to return with an error connection. – midnite11 Mar 06 '20 at 20:42

0 Answers0