如何连接到 R 中的 Oracle 数据库？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5479795/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I connect to an Oracle Database in R?
提问by AME
I am trying to use the RODBC package to query data from an Oracle database using R. There seems to be a great deal of documentation on accessing MySQL databases from R, but not much information on Oracle databases.
我正在尝试使用 RODBC 包从使用 R 的 Oracle 数据库查询数据。似乎有大量关于从 R 访问 MySQL 数据库的文档，但关于 Oracle 数据库的信息并不多。
How do I create a DSN for my Oracle Database?
如何为我的 Oracle 数据库创建 DSN？
采纳答案by DCookie
I'm not familiar with "R", however:
我不熟悉“R”，但是：
Oracle ODBC DSN is generally set up by -
Oracle ODBC DSN 通常由-
- installing the instant client
- using the windows ODBC manager to create the DSN
- 安装即时客户端
- 使用 windows ODBC 管理器创建 DSN
回答by jbryer
Here are the instructions I developed for our site:
以下是我为我们的网站开发的说明：
- Install Oracle instant client. The files to extract are: instantclient-basic-win32-11.1.0.7.0.zip instantclient-odbc-win32-11.1.0.7.0.zip instantclient-sqlplus-win32-11.1.0.7.0.zip (optional)
- 安装 Oracle 即时客户端。要提取的文件是：instantclient-basic-win32-11.1.0.7.0.zip instantclient-odbc-win32-11.1.0.7.0.zip instantclient-sqlplus-win32-11.1.0.7.0.zip（可选）
Note: The Basic Instant Client and ODBC Instant Client packages must be unzipped into same directory. For example, C:\Oracle\instantclient_11_1.
注意：Basic Instant Client 和 ODBC Instant Client 包必须解压缩到同一目录中。例如，C:\Oracle\instantclient_11_1。
Oracle download site: http://www.oracle.com/technology/software/tech/oci/instantclient/htdocs/winsoft.html
Oracle 下载站点：http: //www.oracle.com/technology/software/tech/oci/instantclient/htdocs/winsoft.html
Add the location of the extracted files to the path environment variable (e.g. C:\oracle\instantclient_11_1) a. Right click "My Computer" on the desktop, click "Manage." b. Right click "Computer Management (Local)", click "Properties." c. On the advanced tab, click the Settings button under "Environment Variables." d. Find the "Path" entry under "System variables" and click the Edit button. e. Add the location path to the end of the "Variable Value" box. Note that items are separated by a semi-colon. f. Click OK to confirm changes. Close the Computer Management window.
Double click the odbc_install.exe file in the Instant client directory.
Open C:\WINDOWS\system32\odbcad32.exe
Add a data source for the oracle database. a. Click the Add button b. Select "Oracle in instantclient11_1" and click Finish. c. Enter the following in the Oracle ODBC Driver Configuration dialog: Data Source Name: DSN Description: Roacle (or whatever) TNS Service Name: SERVER:1521/DSN - Change this User ID: Your oracle user name d. Click the Test Connection button. You will be prompted for your password, and if everything went well, you will get a "Connection successful" message.
将解压文件的位置添加到路径环境变量中（例如 C:\oracle\instantclient_11_1） a．右键单击桌面上的“我的电脑”，单击“管理”。湾 右键单击“计算机管理（本地）”，单击“属性”。C。在高级选项卡上，单击“环境变量”下的“设置”按钮。d. 在“系统变量”下找到“路径”条目，然后单击“编辑”按钮。e. 将位置路径添加到“变量值”框的末尾。请注意，项目由分号分隔。F。单击确定以确认更改。关闭计算机管理窗口。
双击 Instant client 目录中的 odbc_install.exe 文件。
打开 C:\WINDOWS\system32\odbcad32.exe
为oracle 数据库添加数据源。一种。单击添加按钮 b。选择“instantclient11_1 中的 Oracle”并单击“完成”。C。在 Oracle ODBC 驱动程序配置对话框中输入以下内容： 数据源名称：DSN 描述：Roacle（或其他） TNS 服务名称：SERVER:1521/DSN - 更改此用户 ID：您的 oracle 用户名 d．单击测试连接按钮。系统将提示您输入密码，如果一切顺利，您将收到“连接成功”消息。
You can then create a channel in R with:
然后，您可以在 R 中创建一个通道：
odbcConnect(dsn, uid = uid, pwd = pwd, readOnly = TRUE)
回答by Andrew Elliott
I found that there are actually some challenges with connecting to Oracle from R. It might just be the implmentation and settings that we have at my company but here is what I did to get it working.
我发现从 R 连接到 Oracle 实际上存在一些挑战。这可能只是我们公司的实施和设置，但这是我为使其正常工作所做的工作。
library(RODBC)
ch=odbcConnect("<AliasForYourDatabase>",pwd = "xxxxxxxx", believeNRows=FALSE)
odbcGetInfo(ch)
The critical step for me was 'believeNRows=FALSE'otherwise I received the following error when I submitted a query:
对我来说关键的一步是，'believeNRows=FALSE'否则我在提交查询时收到以下错误：
"Error in .Call(C_RODBCFetchRows, attr(channel, "handle_ptr"), max, buffsize, : negative length vectors are not allowed"
“.Call(C_RODBCFetchRows, attr(channel, "handle_ptr"), max, buffsize, 中的错误：不允许负长度向量）
This is because Oracle is not providing the actual number of rows back to the connection.
这是因为 Oracle 没有向连接提供实际的行数。
回答by user761758
In case anyone else stumbles on this old question and needs help connecting Oracle and R, this PDF explains the process fully and provides needed download information.
万一其他人偶然发现这个老问题并需要帮助连接 Oracle 和 R，此 PDF 完整解释了该过程并提供了所需的下载信息。
{http://cran.fhcrc.org/web/packages/RODM/RODM.pdf}
{http://cran.fhcrc.org/web/packages/RODM/RODM.pdf}
