RStudio makes Oracle accessibility from R easier via odbc and connections Pane1.
Personally, I find it’s not so easy.
As it finally works for me, I will detail some snippets here.
After tens of try it seems good to share some tricks2. This blog post is also a notepad for me.
Oracle and R configuration is a step where we potentially waste a lot of time.
Many things can cause oracle and R not to work at all:
- it depends on which client is installed (32b, 64b ?)
- wether odbc driver is correctly installed or not
- you have to dissect tnsnames.ora
- investigate on many ORA error’s
- maybe try to clean install Oracle client
Often ROracle is used and it works well, sometimes it doesn’t (some oci.dll not found3, etc.). But it doesn’t work with dplyr/dbplyr at the moment.
After several years with ROracle, I’m happy to have both possibilities for query writing and collecting (SQL, and now dplyr)
Here we are:
RStudio connection Pane
From connection Pane we take Oracle odbc driver name, we have two here for two Oracle client versions:
We now have a big component of the connection string.
32b or 64b
If your Oracle client is 32bit, you have to switch to R 32bits, otherwhise it doesn’t work (at least for me).
Then stackoverflow history helped me4 to structure the entire string:
You will find all these informations in tnsnames.ora. Port is probably 1521.
Some dplyr/dbplyr statements
dplyr and dblink
If you have another oracle database with dblinks it may also works like this:
Catalog of all columns5
Decomposing the connection string
In order to ask for password, we split the connection parts:
see here for a readme in a repo on github: https://github.com/GuillaumePressiat/oracle_odbc_connection_template_for_R ↩
see here for ROracle difficulties: https://technology.amis.nl/2017/08/23/r-and-the-oracle-database-using-dplyr-dbplyr-with-roracle-on-windows-10/ ↩
for Oracle catalogs, see here: https://docs.oracle.com/pls/db92/db92.catalog_views?remark=homepage ↩