I am having a lot of difficulty connecting to Oracle databases using R and I was looking if I could get any help or suggestions.
For example, I am able to connect to the same databases using Microsoft PowerBI, or SQL Server Management Studio (SSMS), but when I try using R or RStudio, I continue to get errors. I am also wondering if there are other packages or ideas I should try to connect with. The server is not public, so I wont put the actual name below, but here is what I have tried.
connectDEV <- DBI::dbConnect(odbc::odbc(),
Driver = "Oracle in instantclient_19_8",
DBQ = "servername:port/folder",
UID = rstudioapi::askForPassword("Database user"),
PWD = rstudioapi::askForPassword("Database password"))
The error I keep getting:
Error: nanodbc/nanodbc.cpp:983: IM002: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
What I have tried:
I have installed the drivers, and have a file C:/Oracle, in that folder I have instant_client_19_8. When I do odbc::odbcListDrivers() I don't see any with the name instantclient, but when I try to install using run as admin, start odbc_install.exe, it quickly flashes and then nothing happens.
What I installed:
Basic Package and ODBC Package
(The odbc package is what had the installer, I unzipped both in the same folder).
Any ideas? What else should I try?
Update: I fixed it. I had to do the following, from the above link in addition to installing the instant client Development and Runtime- optional packages, download 'ODBC'