I'm pretty new to R and may be I'm re-asking this question again, but I'm posting this as I didn't find an appropriate answer.
I'm trying to connect to Oracle DB that sits on a remote location from R using RODBC. I went through a lot of RODBC configuration documents but nothing clearly specifies connection to remote Oracle DB and what is the root cause.
I also pondered on using ROracle as DBI but found out that RODBC fares much better for multiple reasons.
So, here's my code:
> # Load RODBC package
> library(RODBC)
> # Create a connection to the database
> dbconnection <- odbcDriverConnect("Driver={Oracle ODBC Driver}; Server=my_hostname; Database=my_db_name; Uid=my_uid; Pwd=my_pwd")
> # Check that connection is working
> odbcGetInfo(dbconnection)
> close(dbconnection)
And the TNS Config that I use [values altered for obvious reasons]:
my_db_name =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = my_hostname)(PORT = my_port))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = my_service_name)
)
)
Note: The other thing here is that I don't have a local Oracle DB setup. I use SQL Developer to connect to the remote Oracle DB with the given TNS Config.
I get the below error:
Warning messages:
1: In odbcDriverConnect("Driver={Oracle ODBC Driver}; Server=my_hostname; Database=my_db_name; Uid=my_uid; Pwd=my_pwd") :
[RODBC] ERROR: state 01000, code 0, message [unixODBC][Driver Manager]Can't open lib 'Oracle ODBC Driver' : file not found
2: In odbcDriverConnect("Driver={Oracle ODBC Driver}; Server=my_hostname; Database=my_db_name; Uid=my_uid; Pwd=my_pwd") :
ODBC connection failed
After installing R, I have only installed RODBC using the install.packages("RODBC") command. Also, I'm running this on Mac OS.
I know I'm missing something here. Specifically the Driver={Oracle ODBC Driver} might need to modified to the actual drivers name. But I don't know what it should be and googling didn't help much.
Is there anything else that I need to install or configure?
