Create a Oracle ODBC DSN with Widows. Entering the port number

Question

I have the following entry in my c:\Oracle\product\11.1.0\network\ADMIN\TNSNAMES.ORA file.

enter code herepvtest.world =

(DESCRIPTION =

(ADDRESS_LIST =

  (ADDRESS = (PROTOCOL = TCP)(HOST = xxxx.xxx.xxx)(PORT = 1521))

)

(CONNECT_DATA =

  (SID = pvtest)

  (SERVER = DEDICATED)

)

)

I could connect successfully using TOAD. Now when I try creating a new ODBC DSN it doesn't allow me to do so. How do I specify the port number in TNS Service Name?

enter code hereGet the following error : ---------------------------

Testing Connection

Unable to connect SQLState=S1000 [Oracle][ODBC][Ora]ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

---

OK

Answer 1

When you say you could connect with TOAD, can you do so now that the ODBC connection fails? if you can connect with any tool on your client PC than obviously the listener at the other end knows of the service so you have to suspect your end. Probably one client tool is trying to reach PVTEST at a diffrerent location than the other.

If one client tool can connect and the other cannot, first of I would confirm that both clients are using the same tnsnames.ora file. Search your client and see if there are other tnsnames.ora files which would imply other ORACLE_HOMES (Google that if you don't know what it is).

From a command line do a TNSPING and see if it reaches the host. It will also tell about the connection details.

In TOAD check the connection details and ensure it is using a tnsnames connection and if so which tnsnames.

Check if your client has the environment variable TNS_ADMIN set.

Answer 2

I have this kind of block with Oracle XE:

XE = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = test.acme.org)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XE) ) )

If you compare that with yours definition, not that there is "SERVICE_NAME" parameter.