使用 ODBC 连接到 Oracle DB

Connect to an Oracle DB using ODBC

提问by markshancock

I am developing an application that needs to interract with a "lightly documented" Legacy Oracle Database. To Start that process I want to start creating a view into that Database using ODBC links into an MS Access database so I can figure out the DB structure but I can't figure out how to setup the ODBC connection to the Oracle DB.

我正在开发一个应用程序，该应用程序需要与“文档较少”的旧版 Oracle 数据库进行交互。要开始该过程，我想开始使用 ODBC 链接到 MS Access 数据库中的数据库创建视图，这样我就可以找出数据库结构，但我无法弄清楚如何设置到 Oracle DB 的 ODBC 连接。

I have been able to connect using the Host and Service Name to and view the DB using SQL Developer; but, I can't figure out how to setup ODBC. I am running Windows 7 and have installed Oracle 11g, Oracle Express Edition, the Instant Client and ODBC extensitons; but on the ODBC setup Oracle wants me to pick a TNS Service Name but there is none to pick and there is no place to specify the host. I tried to setup a TNS in tnsnames.ora; but I am not really sure I know the right location for that file.

我已经能够使用主机和服务名称连接到并使用 SQL Developer 查看数据库；但是，我不知道如何设置 ODBC。我运行的是 Windows 7 并安装了 Oracle 11g、Oracle Express Edition、Instant Client 和 ODBC 扩展；但是在 ODBC 设置中，Oracle 希望我选择一个 TNS 服务名称，但没有选择，也没有地方指定主机。我试图在 tnsnames.ora 中设置一个 TNS；但我不确定我是否知道该文件的正确位置。

I really thought this would be the easy part; but, it really hasn't been.

我真的认为这将是容易的部分；但是，它真的没有。

采纳答案by eabraham

Navigate to the Control Panel> Administrative Tools> Data Sources (ODBC)

导航到控制面板>管理工具>数据源 (ODBC)

Select the System DSNtab and click 'Add'. Next scroll down the lists of drivers until you find Microsoft ODBC for Oracle.

选择系统 DSN选项卡，然后单击“添加”。接下来向下滚动驱动程序列表，直到找到Microsoft ODBC for Oracle。

Fill in the required information in the above form and click 'OK'. Now you can add the tables to 'Access' by clicking on the 'External Data' tab then clicking on 'More'. Choose 'ODBC Databases', then 'Import the source data...', next click on the 'Machine Data Source' tab.

在上述表格中填写所需信息，然后单击“确定”。现在您可以通过单击“外部数据”选项卡然后单击“更多”将表添加到“访问”。选择“ODBC 数据库”，然后“导入源数据...”，然后单击“机器数据源”选项卡。

回答by Justin Cave

The simplest option to generate the tnsnames.ora file is to let Oracle do it. If you launch the Oracle Net Configuration Assistant (Start | Oracle in Oracle Home Name| Configuration and Migration Tools | Net Configuration Assistant), you should be able to choose "Local Net Service Name configuration" which allows you to add (or modify) a TNS alias. That will walk you through gathering the information you'll need to connect.

生成 tnsnames.ora 文件的最简单选项是让 Oracle 来做。如果您启动 Oracle Net Configuration Assistant（开始 | Oracle 中的Oracle Home Name| Configuration and Migration Tools | Net Configuration Assistant），您应该能够选择“Local Net Service Name configuration”，它允许您添加（或修改） TNS 别名。这将引导您收集连接所需的信息。

If you are more comfortable dealing with the text files directly, you can directly edit the tnsnames.ora file in %Oracle Home%\network\ADMIN\tnsnames.ora using this as a template.

如果您更习惯于直接处理文本文件，则可以使用它作为模板直接编辑 %Oracle Home%\network\ADMIN\tnsnames.ora 中的 tnsnames.ora 文件。

<TNS alias> = 
  (DESCRIPTION = 
    (ADDRESS = (PROTOCOL = TCP)(HOST = <hostname or IP>)(PORT = <port>)) 
    (CONNECT_DATA = 
      (SERVER = DEDICATED) 
      (SERVICE_NAME = <database service name>) 
    ) 
  ) 

回答by Dave

In tnsnames.ora, try changing SERVICE_NAME to SID. That worked for me.

在 tnsnames.ora 中，尝试将 SERVICE_NAME 更改为 SID。那对我有用。

回答by Akihiro A Kaneko

My Experience 1. TNSNAMES.ORA is as follows. XE = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.2.116) (PORT = 1521) ) ) (CONNECT_DATA = (SERVICE_NAME = XE) ) )

我的经验 1. TNSNAMES.ORA 如下。XE = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.2.116) (PORT = 1521) ) ) (CONNECT_DATA = (SERVICE_NAME = XE) ) )

1. Set Windows Environment Variables (ControlPanel --> System --> Detail..) 2-1. Add to PATH c:\oraclexe\instantclient_11_2\ --- install directory of instantclient 2-2. Add New Environment Variable TNS_ADMIN c:\oraclexe\instantclient_11_2\ --> install directory NLS_LANG = JAPANESE_JAPAN.JA16SJISTILDE
2. Windows command prompt cd c:\Windows\SysWow64 <--I use 32bit ODBC in 64bit Win7 odbcad32.exe 　 Name : ICODBC <-- as you like Service Name : XE User Name: system Press Connection Test Button

2. 设置 Windows 环境变量（ControlPanel --> System --> Detail..） 2-1．添加到PATH c:\oraclex\instantclient_11_2\--​​-instantclient 2-2的安装目录。添加新环境变量 TNS_ADMIN c:\oraclex\instantclient_11_2\ --> 安装目录 NLS_LANG = JAPANESE_JAPAN.JA16SJISTILDE
3. Windows 命令提示符 cd c:\Windows\SysWow64 <--I use 32bit ODBC in 64bit Win7 odbcad32.exe Name : ICODBC <-- as you like 服务名称：XE 用户名：system Press Connection Test Button