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

Question

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:

I am using my router software's VPN and port-forwarding, and those must be configured right enough for the DSN connection to succeed, pointing to the router's WAN IP.
I've tried replacing my server's name with my router's IP (which my client system code is pointing to), and with my server's local IP, in both my tnsnames.ora and my listener.ora. I think I've tried every single combination there, stopping and starting the whole server each time, and waiting a minute after starting. With most of these combinations, actually, the DSN connection fails with listener does not currently know of service requested in connect descriptor. Meanwhile, the non-DNS connection fails with my good friend "protocol adapter error."
I've tried using the server's local IP on my client, while my client is within the same LAN, and with that local IP in the .ora file(s). Same error. Simple IP pings work.

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.

user_4522216 · Answer

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

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

1 Answers