TNS service names ODBC dropdown box garbled, messed up, gibberish

 2011-09

本文仅记录下我解决ODBC 中向 TNS Service Name 下拉框添加数据库服务器选项的问题。

Most likely cause is that there are two or more different Oracle clients installed, with two or more home folders and tnsnames.ora files.

For example, an Oracle 10 client is installed or Oracle XE is installed. Both contain a home folder with tnsnames.ora. Later, the Oracle 11g instant client + ODBC compontents is added. Now there are two home folders.

In this case, the TNS_ADMIN environment variable needs to exist:

• Create TNS_ADMIN variable via control panel -> system -> advanced -> environment variables -> system variables
• Set is to one of the admin folders, eg.:
  

  TNS_ADMIN=D:/Database_installation/oracle/product/10.2.0/db_1/NETWORK/ADMIN

• picturce as

• Note: make sure it contains the tnsnames.ora, sqlnet.ora and listener.ora files.
• Create a ODBC connection using the Oracle driver and check if the "tns service name" dropdown box contains readable entries.

below is my original files

--- tnsnames.ora file------

# tnsnames.ora Network Configuration File: D:/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

PROJ35 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.50.0.35)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = proj35)
    )
  )

dbs107 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.50.0.107)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = dbs107)
    )
  )

--------------------listener.ora  file-----------------

# listener.ora Network Configuration File: D:/oracle/product/10.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = D:/Database_installation/oracle/product/10.2.0/db_1)
      (PROGRAM = extproc)
    )

(SID_DESC =

(GLOBAL_DBNAME = ORACLE)

(ORACLE_HOME = D:/Oracle/Oracle10g)

(SID_NAME = ORACLE)

)
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = tidemark.achievo.com)(PORT = 1521))
    )
  )