CODEWITHSUNDEEP
roracle-databaseodbc
DHW
DHW

Reputation: 1267

Using the new odbc package with an IP address and no DSN with an Oracle server

I can get the odbc package working in Windows so long as I add a DSN for my Oracle server, but I can't get it working at all otherwise. Namely, after adding a DSN entry named "DummyDB" which contains just an IP address and service ID (in addition to the driver, of course), this works:

conn <- dbConnect(odbc(), 
                  DSN = "DummyDB",
                  UID = credentials$login,
                  PWD = credentials$pw,
                  Port = 1521)

and yet this, with the exact same IP, driver, and service ID as in the DSN entry, does not (IP x'ed out for privacy):

conn <- dbConnect(odbc(), 
                  Driver = "Oracle in IC",
                  Host = "xx.xxx.xx.xxx",
                  SVC = "XE",
                  UID = credentials$login,
                  PWD = credentials$pw,
                  Port = 1521)

nor does this work:

conn <- dbConnect(odbc(), .connection_string = "Driver={Oracle in IC};Server=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=xx.xxx.xx.xxx)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=xe)));Uid=xxx;Pwd=xxx;")

And of course I've tried tweaking all of these every which way. No matter what, I get the always-mysterious:

nanodbc/nanodbc.cpp:950: HY000: [Oracle][ODBC][Ora]ORA-12560: TNS:protocol adapter error

Obligatory details:

Perhaps a DSN added via the Data Source Administrator is a hard requirement here for some reason? At this point it'd be great to just have any example of someone successfully connecting to any (Oracle) server with this package but without using a client-side DSN entry.

Upvotes: 4

Views: 1944

Answers (1)

strange attractor
strange attractor

Reputation: 86

I was having an identical problem and I tried all the same things. I finally resolved it by changing the Host argument to DBQ and adding the port and service name there:

vdw = DBI::dbConnect(odbc::odbc(), 
                     Driver= "My Oracle in Dir", # e.g. "Oracle in OraClient12Home1"
                     DBQ = "my-host-name.domain.org:1521/my-service-name",
                     Schema = "my_schema",
                     UID="my_userid",
                     PWD="my_password)

Hope that helps! This took me far too long to figure out... the answer was here: RODBC connectivity to Oracle without tnsnames.ora

Upvotes: 7

Related Questions