Oracle ODBC Connection now always requires host, port and SVC

[alan_y]

An Oracle database connection used to work (on the open source version of RStudio Server) using just the following details:

connection <- odbc::dbConnect(
    drv = odbc::odbc(),
    dsn = "dsn_name",
    uid = "my_uid",
    pwd = "my_pwd")

Notice that it never needs specific details like host, port and SVC.
But now when I try on RStudio Server Pro, it seems to make it essential to also enter details on host, port number and SVC every time.

Error: nanodbc/nanodbc.cpp:950: 08001: [unixODBC][RStudio][ODBC] (10380) Unable to establish a connection with data source. Missing settings: {[HOST] [PORT] [SVC]}

My feeling is that this may be an issue with how the Oracle drivers have been set up on RStudio Server Pro - would anybody be able to offer some advice please?

[Blair09M]

Hi @alan_y and welcome to RStudio Community! It looks like you're using a DSN and trying to pass UID and PWD from the R session. This will work as long as HOST, PORT, and SVC are specified in the DSN itself. These DSNs are typically defined in /etc/odbc.ini. As an example, a DSN for Oracle might look like the following:

[Oracle DSN]
driver = Oracle
host = <host address>
svc = <svc>
port = 1521
EnableTableTypes = 1

Given this configuration, the R code you referenced should work. Feel free to provide further input if that doesn't resolve the issue.

[alan_y]

Thanks @Blair09M, I set up a personal .odbc.ini file with the necessary settings to test this as I don't have write access to the global odbc.ini and this worked! Now I just need to get our IT department to make the changes. Much appreciated!