Connecting to Oracle DB with odbc library fails on macOS 10.15.5

General
,
2

Hi @nickodemus,

It looks to me like odbcinst -j is returning the correct output, and you are able to successfully run isql, so I think you're almost there. For some reason, your R code isn't actually making use of the DSN you're configuring, and therefore R is looking in the wrong place for the driver.

Can you share the isql command that's running successfully? That might be a hint here.

It looks like you've got a few different pieces configured here, but the simplest pattern is usually to:

  1. Define the DSN in odbc.ini with a name. In this case, it looks like you've defined a DSN named banner in the odbc.ini.
  2. Use that DSN in your DBI code. Since your DSN is named banner, your DBI code would look something like
    con <- DBI::dbConnect(odbc::odbc(), "banner", <additional arguments not in the odbc.ini>),
  3. The odbc.ini will either directly specify the driver path, or use a name defined in odbcinst.ini to look up the driver path. It looks like your odbc.ini is directly specifying the driver, so you shouldn't need the odbcinst.ini at all.

There's a little more of a step-by-step walkthrough here: https://db.rstudio.com/best-practices/drivers/

It looks to me like steps 1-6 above (thanks for the super-thorough walkthrough, btw) are about getting the Oracle driver to work properly, but now it's just about making sure the R install knows how to find it.

What happens if you try connecting using the DSN name?