Connect to Oracle database using ROracle with tnsn

问题:

Followup to Installation of RODBC/ROracle packages on OS X Mavericks...

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")

, I get the error

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

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

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.

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)

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!

回答1:

I've seen many recommendations online for adding environment variables on Yosemite to be accessed by RStudio. The only one that has fully worked for me, at least so far, is to add all environment variables to the plist file. You can add as many variables as you want, which is described by @MortimorGoro in Setting environment variables via launchd.conf no longer works in OS X Yosemite/El Capitan/macOS Sierra?.

So my solution here was to just add TNS_ADMIN to plist!

回答2:

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)

testsql <- "select sysdate from dual;"

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

1 Click on the Start button (in windows 7)

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

3 Add your connection based off your TNS file.

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

Hope this helps!