0

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.

Andreas
  • 6,612
  • 14
  • 59
  • 69
  • use `ROracle` package? this will require Oracle instant client to be unzipped into a folder locally – chinsoon12 Jul 20 '18 at 07:57
  • Well. I Tried but got `Package which is only available in source form, and may need compilation of C/C++/Fortran: ‘ROracle’`. Various solutions to this was e.g. install "RTools" package - but that didn't help. Another solution was to choose another repo (my default is a rstudio mirror) - but that did'nt work either. – Andreas Jul 21 '18 at 09:40
  • U can install Rtools into a folder that does not require admin rights – chinsoon12 Jul 21 '18 at 12:12
  • Ahhh - I tried to downlaod rtools via install.packages - but I donwload it manually from Cran. Now I can compile ROracle. But I need an oracle account to download the instant client. And it seems the account confirmation email is caught in my spamfilter. Will report my progress tomorrow. :-) – Andreas Jul 22 '18 at 20:38
  • Got the instant client unzipped locally. But the install fails with this message: "ERROR: cannot find Oracle Client. Please set OCI_LIB64 to specify its location." - I guess this is a system enivoronment variable i win 7? Which I need an admin account to change. Is that correct? – Andreas Jul 22 '18 at 20:50
  • You can just use Sys.setenv to set the OCI_LIB64 path – chinsoon12 Jul 22 '18 at 22:04
  • Ahh- Thank you so much @chinsoon12.. Now I get this: "ERROR: cannot find C:/Users/xxxxx/Documents/R/instantclient_12_2//sdk/include or Oracle Client include. Please set OCI_INC to specify its location." Any idea what this is? I have somethink called "C:\Program Files (x86)\Oracle\JInitiator 1.3.1.22" - is this relevant? – Andreas Jul 23 '18 at 08:14
  • you need to unzip both the instantclient Basic Package and SDK Package. now that you are very close to get ROracle to run, read this: http://cran.us.r-project.org/web/packages/ROracle/INSTALL. it will ease your pain and clarify your understanding of the installation steps – chinsoon12 Jul 23 '18 at 09:05
  • Ahh - so I see that the SDK kan be downloaded separately. After I've done this (and installed "DBI"), I set up the environment variable. Now I get this: "Warning: package 'DBI' was built under R version 3.5.1 Error: package or namespace load failed for 'ROracle' in inDL(x, as.logical(local), as.logical(now), ...):" Then the error message says that R can read the shared object: 'C:/Users/xxxx/Documents/R/win-library/3.5/ROracle/libs/x64/ROracle.dll': LoadLibrary failure: The specified module could not be found. – Andreas Jul 23 '18 at 10:40
  • U will need to install.packages(“ROracle”, type=“source”, INSTALL_opts=“—no-multiarch”) – chinsoon12 Jul 23 '18 at 10:56
  • Still get "package or namespace load failed for 'ROracle' in inDL(x, as.logical(local), as.logical(now), ...):" And could not read shared object " 'C:/Users/xxxx/Documents/R/win-library/3.5/ROracle/libs/x64/ROracle.dll': LoadLibrary failure: The specified module could not be found." I have installed the instant client and sdk in 64 bit versions. Should I try with 32 bit? - R is running in 64 bit though. Its complaining that oci.dll is missing – Andreas Jul 23 '18 at 11:06
  • There are 2 ways to fix this. One is add the path to oci.dll in your system environment PATH or use dyn.load(path to oci.dll). Let me know if u r still facing problem. – chinsoon12 Jul 23 '18 at 12:10

2 Answers2

1

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.

DRC
  • 43
  • 1
  • 6
0

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.

Wil
  • 3,076
  • 2
  • 12
  • 31