Connect to ORACLE via R, using the info in sql dev

Question

I am working on a machine without admin rights. I use sql developer to connect to an internal database. I would like to connect via R also.

Is there any way I can do this, without admin rights? Some solutions require me to set up a systemDNS - which I can not do. Other requires me to install jvm.dll

My environment: Windows7, sqldeveloper, connection method is via TNS file.

Answer 1

You can set the environment variables in your R session.

Sys.setenv(OCI_LIB64="/Path/to/instantclient",OCI_INC="/Path/to/instantclient/sdk/include")

You can put this in the file .Rprofile in your home directory, and RStudio will run it each time you begin a new session. Once you have this in .Rprofile you should be able to install ROracle.

Answer 2

Connecting to SQL Developer via R is far more difficult than other databases I've encountered. It's important that you have jdbc6.jar installed on your machine, and that you know the file path to where it was installed. Installing the jar file does not require admin rights. You can install the jar file from Oracle's website.

I use the RJDBC package to connect like so:

    library(RJDBC)

    jdbcDriver <- JDBC("oracle.jdbc.OracleDriver", classPath = "file path to where ojdbc6.jar is installed on your computer")

    jdbcConnection <- dbConnect(jdbcDriver, "jdbc:oracle:thin:@YOUR_SERVER","YOUR_USERNAME","YOUR_PASSWORD")

You can then test the connection with a number of commands; I typically use:

    dbListTables(jdbcConnection)

Another favorite of mine is to use dbplyr for dplyr-like functions when working with databases:

    library(dbplyr)

    tbl(jdbcConnection, "SAMPLE_TABLE_NAME")

The resulting output will be the data from the queried table in tibble form.