在Mac OS 10.10上使用带有tnsnames.ora的ROracle连接到Oracle数据库 [英] Connect to Oracle database using ROracle with tnsnames.ora on Mac OS 10.10

问题描述

关注在OS X上安装RODBC/ROracle软件包小牛 ...

首先，我已经使用@joran提供的答案在Mac OS 10.10.3(Yosemite)上安装了ROracle.另外，使用启动plist文件设置DYLD_LIBRARY_PATH，我可以运行library(ROracle)，并且加载得很好.但是，我无法使用tnsnames.ora文件连接到我的数据库.我已将TNS_ADMIN变量添加到.Renviron文件中，RStudio似乎可以使用该文件:

First of all, I have installed ROracle on Mac OS 10.10.3 (Yosemite) using the answer provided by @joran. Additionally, using the start-up plist file to set DYLD_LIBRARY_PATH, I can run library(ROracle), and it loads just fine. However, I am unable to connect to my database with a tnsnames.ora file. I have added the TNS_ADMIN variable to the .Renviron file, which RStudio seems to pick up:

> Sys.getenv("TNS_ADMIN")
[1] "opt/oracle/instantclient_11_2/network/admin"

例如，当我运行以下命令时

When I run the following, for example

con <- dbConnect(drv = dbDriver("Oracle"), dbname = "db", username = "user", password = "pw")

，我收到错误消息

Error in .oci.Connect(.oci.drv(), username = username, password = password,  : 
  ORA-12154: TNS:could not resolve the connect identifier specified

此外，我还向.bash_profile添加了TNS_ADMIN环境变量，但这无济于事.

In addition, I have also added the TNS_ADMIN environment variables to .bash_profile, but that didn't help.

注1:我已经使用tnsnames.ora文件通过SQL Developer连接到数据库，所以我很确定问题出在文件内容之外.

NOTE 1: I have already used the tnsnames.ora file to connect to the database with SQL Developer, so I'm fairly confident the issue is something external to the content of the file.

注2:实际上，我可以使用ROracle与以下内容进行连接:

NOTE 2: I can in fact connect using ROracle with something like:

# see example at http://www.oralytics.com/2015/05/loading-json-data-into-oracle-using.html
host <- "localhost"
port <- 1521
service <- "pdb12c"
drv <- dbDriver("Oracle")

connect.string <- paste(

"(DESCRIPTION=",

"(ADDRESS=(PROTOCOL=tcp)(HOST=", host, ")(PORT=", port, "))",

"(CONNECT_DATA=(SERVICE_NAME=", service, ")))", sep = "")

con <- dbConnect(drv, username = "dmuser", password = "dmuser", dbname = connect.string)

我仔细检查了tnsnames.ora文件，它的格式与connect.string完全相同，所以我认为RStudio并未真正看到它，即使Sys.getenv("TNS_ADMIN")给了我正确的路径.另外，可能是Mac上dbname参数要求的名称与Windows不同.

I double checked my tnsnames.ora file and it's in the exact same format as connect.string, so I'm thinking it's just not actually being seen by RStudio, even though Sys.getenv("TNS_ADMIN") gives me the correct path. Alternatively, it could be that the name required by the dbname argument on Mac is different than Windows.

任何帮助将不胜感激！谢谢！

Any help would be greatly appreciated! Thanks!

推荐答案

对于那些来到这里但在Windows 7上的人，在我的工作站上，我必须使用TNSnames.ora文件(也要使用TNS_ADMIN环境变量)(位于:C:\ app-请参见第二个屏幕截图)，我可以执行以下操作:

For those of you that got here but are on Windows 7, on my workstation I have to use a TNSnames.ora file (also utilizing TNS_ADMIN environment variable)(Located in: C:\app - see 2nd screenshot), I got the following to work:

library(RODBC)

channel <- odbcConnect("PERMIT_DEV_odbc", uid = "POWDERED_TOAST_MAN", pwd = "dev_NONE_OF_YOUR_BEEZNEEZ", believeNRows = FALSE)

channel <- odbcConnect("PERMIT_DEV_odbc", uid = "POWDERED_TOAST_MAN", pwd = "dev_NONE_OF_YOUR_BEEZNEEZ", believeNRows = FALSE)

testsql <- "select sysdate from dual;"

query <- sqlQuery(channel = channel, query = testsql)

query <- sqlQuery(channel = channel, query = testsql)

1单击开始"按钮(在Windows 7中)

1 Click on the Start button (in windows 7)

2开始键入"odbc"，然后查找设置数据源(ODBC)"或类似的内容.

2 Start typing, "odbc" and look for "set up data sources (ODBC)" or something like that.

3基于TNS文件添加连接.

3 Add your connection based off your TNS file.

4还有一个测试连接"按钮，您应使用该按钮来验证连接性！

4 there's also a "test connection" button you should use to verify connectivity!

希望这会有所帮助！

这篇关于在Mac OS 10.10上使用带有tnsnames.ora的ROracle连接到Oracle数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！