Skip to main content

Installing and using ROracle in R

Hi,
Hope this post keeps you in the best of health. I am an oracle user and wanted to know how to fetch database information in R. There is a package out there called ROracle but there are no binaries for it and it thus needs to be built and then installed. Here are the steps to install it on Windows 7 machines.

1. Download the package from http://cran.r-project.org/web/packages/ROracle/index.html. Since I wrote this post the latest that was available was ROracle_1.1-12.tar.gz.
2. Place the package in the directory where R is installed. I placed mine in E:\R\R-3.0.2\bin folder.
3. Install RTools from http://cran.r-project.org/bin/windows/Rtools/. Since my R version is R-3.0.2 the toolkit I needed was RTools31.exe.
4. Install the Rtools software in the R home directory. I placed mine in E:\R\Rtools. Place all the extras in there too. For example I placed my 32 bit extras in E:\R\RExtras32 and the 64 bit in E:\R\RExtras64 folder. These extras are not necessary for ROracle and only required when building R packages. if you get an error like cannot write to registry just ignore that.
5. Open a command window.
6. Use the following commands
a. set path=E:\R\Rtools\bin;E:\R\Rtools\gcc-4.6.3\bin;%PATH%
b. SET OCI_INC=c:\app\oracle\product\11.2.0\client_32\oci\include
c. set OCI_LIB32=c:\app\oracle\product\11.2.0\client_32\BIN
d. set OCI_LIB64=c:\app\oracle\product\11.2.0\client_64\BIN
e. R CMD INSTALL --build --merge-multiarch ROracle_1.1-12.tar.gz

7. If you see something like this below then everything is fine.

install for i386

* installing to library 'E:/R/R-3.0.2/library'
* installing *source* package 'ROracle' ...
** package 'ROracle' successfully unpacked and MD5 sums checked
cygwin warning:
  MS-DOS style path detected: c:\app\oracle\product\11.2.0\client_32\BIN
  Preferred POSIX equivalent is: /cygdrive/c/app/oracle/product/11.2.0/client_32/BIN
  CYGWIN environment variable option "nodosfilewarning" turns off this warning.
  Consult the user's guide for more details about POSIX paths:
    http://cygwin.com/cygwin-ug-net/using.html#using-pathnames
Oracle Client Shared Library 32-bit - 11.2.0.2.0 Operating in ORACLE_HOME environment.
found Oracle Client c:\app\oracle\product\11.2.0\client_32\BIN
found Oracle Client include c:\app\oracle\product\11.2.0\client_32\oci\include
copying from c:\app\oracle\product\11.2.0\client_32\oci\include
** libs
cygwin warning:
  MS-DOS style path detected: E:/R/R-30~1.2/etc/i386/Makeconf
  Preferred POSIX equivalent is: /cygdrive/e/R/R-30~1.2/etc/i386/Makeconf
  CYGWIN environment variable option "nodosfilewarning" turns off this warning.
  Consult the user's guide for more details about POSIX paths:
    http://cygwin.com/cygwin-ug-net/using.html#using-pathnames
gcc -m32 -I"E:/R/R-30~1.2/include" -DNDEBUG -I./oci    -I"d:/RCompile/CRANpkg/extralibs64/local/include"     -O3 -Wall  -std=gnu99 -mtune=core2 -c rodbi.c -o rodbi.o
gcc -m32 -I"E:/R/R-30~1.2/include" -DNDEBUG -I./oci    -I"d:/RCompile/CRANpkg/extralibs64/local/include"     -O3 -Wall  -std=gnu99 -mtune=core2 -c rooci.c -o rooci.o
In file included from E:/R/R-30~1.2/include/R.h:44:0,
                 from rodbi.h:38,
                 from rooci.c:79:
E:/R/R-30~1.2/include/R_ext/RS.h:45:0: warning: "ERROR" redefined [enabled by default]
e:\r\rtools\gcc-4.6.3\bin\../lib/gcc/i686-w64-mingw32/4.6.3/../../../../i686-w64-mingw32/include/wingdi.h:70:0: note: this is the location of the previous definition
gcc -m32 -shared -s -static-libgcc -o ROracle.dll tmp.def rodbi.o rooci.o c:\app\oracle\product\11.2.0\client_32\BIN/oci.dll -Ld:/RCompile/CRANpkg/extralibs64/local/lib/i386 -Ld:/RCompile/CRANp
kg/extralibs64/local/lib -LE:/R/R-30~1.2/bin/i386 -lR
installing to E:/R/R-3.0.2/library/ROracle/libs/i386
** R
** inst
** preparing package for lazy loading
** help
*** installing help indices
** building package indices
** installing vignettes
** testing if installed package can be loaded

install for x64

* installing to library 'E:/R/R-3.0.2/library'
* installing *source* package 'ROracle' ...
** package 'ROracle' successfully unpacked and MD5 sums checked
cygwin warning:
  MS-DOS style path detected: c:\app\oracle\product\11.2.0\client_64\BIN
  Preferred POSIX equivalent is: /cygdrive/c/app/oracle/product/11.2.0/client_64/BIN
  CYGWIN environment variable option "nodosfilewarning" turns off this warning.
  Consult the user's guide for more details about POSIX paths:
    http://cygwin.com/cygwin-ug-net/using.html#using-pathnames
Oracle Client Shared Library 64-bit - 11.2.0.2.0 Operating in ORACLE_HOME environment.
found Oracle Client c:\app\oracle\product\11.2.0\client_64\BIN
found Oracle Client include c:\app\oracle\product\11.2.0\client_32\oci\include
copying from c:\app\oracle\product\11.2.0\client_32\oci\include
** libs
cygwin warning:
  MS-DOS style path detected: E:/R/R-30~1.2/etc/x64/Makeconf
  Preferred POSIX equivalent is: /cygdrive/e/R/R-30~1.2/etc/x64/Makeconf
  CYGWIN environment variable option "nodosfilewarning" turns off this warning.
  Consult the user's guide for more details about POSIX paths:
    http://cygwin.com/cygwin-ug-net/using.html#using-pathnames
gcc -m64 -I"E:/R/R-30~1.2/include" -DNDEBUG -I./oci    -I"d:/RCompile/CRANpkg/extralibs64/local/include"     -O2 -Wall  -std=gnu99 -mtune=core2 -c rodbi.c -o rodbi.o
gcc -m64 -I"E:/R/R-30~1.2/include" -DNDEBUG -I./oci    -I"d:/RCompile/CRANpkg/extralibs64/local/include"     -O2 -Wall  -std=gnu99 -mtune=core2 -c rooci.c -o rooci.o
In file included from E:/R/R-30~1.2/include/R.h:44:0,
                 from rodbi.h:38,
                 from rooci.c:79:
E:/R/R-30~1.2/include/R_ext/RS.h:45:0: warning: "ERROR" redefined [enabled by default]
e:\r\rtools\gcc-4.6.3\bin\../lib/gcc/i686-w64-mingw32/4.6.3/../../../../i686-w64-mingw32/include/wingdi.h:70:0: note: this is the location of the previous definition
gcc -m64 -shared -s -static-libgcc -o ROracle.dll tmp.def rodbi.o rooci.o c:\app\oracle\product\11.2.0\client_64\BIN/oci.dll -Ld:/RCompile/CRANpkg/extralibs64/local/lib/x64 -Ld:/RCompile/CRANpk
g/extralibs64/local/lib -LE:/R/R-30~1.2/bin/x64 -lR
installing to E:/R/R-3.0.2/library/ROracle/libs/x64
** testing if installed package can be loaded
* MD5 sums
packaged installation of 'ROracle' as ROracle_1.1-12.zip
* DONE (ROracle)

8. Open RStudio and run library(ROracle). You will get a message Loading required package: DBI
9. use dbConnect to connet to an Oracle database.
con <- dbConnect(Oracle(), user="andrnev", password="xxx", dbname="LNMXPFP1.WORLD")
10. Query the database
query <- dbSendQuery(con, “SELECT * from MX_MARKET_DATA.V_FX_VALUE”)
11. fetch the output. Result is stored as a data.frame
result <- fetch(query)

Note: The steps are shown for a standard Oracle client installation or an Oracle home based client for version 11.2.0. This software is available free after signing up with Oracle.
Labels:

Comments

  1. Unknown23 October 2015 at 06:09

    Hi Neville, I'm trying to do something similar, but with Oracle 11g (also on windows 7). However, once I get to the copying from OCI step, I get a bunch of errors. Command prompt code is below:

    C:\Users\pbm887>cd C:\Program Files\R\R-3.2.2\bin
    C:\Users\pbm887>set path=C:\Program Files\R\Rtools\bin;C:\Program Files\R\Rtools\gcc-4.6.3\bin;%PATH%
    C:\Program Files\R\R-3.2.2\bin>set OCI_LIB64=C:\Program Files (x86)\Oracle 11g\11g\bin
    C:\Program Files\R\R-3.2.2\bin>set OCI_INC=C:\Program Files (x86)\Oracle 11g\11g\oci\include
    C:\Program Files\R\R-3.2.2\bin>R CMD INSTALL --build ROracle_1.2-1.tar.gz

    and I get this as a result:

    * installing to library 'C:/Program Files/R/R-3.2.2/library'
    * installing *source* package 'ROracle' ...
    ** package 'ROracle' successfully unpacked and MD5 sums checked
    test: Files: unknown operand
    test: Files: unknown operand
    found Oracle Client C:\Program Files (x86)\Oracle 11g\11g\bin
    found Oracle Client include C:\Program Files (x86)\Oracle 11g\11g\oci\include
    test: Files: unknown operand
    copying from C:\Program Files (x86)\Oracle 11g\11g\oci\include
    cp: cannot stat 'C:\\Program': No such file or directory
    cp: cannot stat 'Files': No such file or directory
    cp: cannot stat '(x86)\\Oracle': No such file or directory
    cp: cannot stat '11g\\11g\\oci\\include/nzerror.h': No such file or directory
    cp: cannot stat 'C:\\Program': No such file or directory
    cp: cannot stat 'Files': No such file or directory
    cp: cannot stat '(x86)\\Oracle': No such file or directory
    cp: cannot stat '11g\\11g\\oci\\include/nzt.h': No such file or directory

    (The errors continue for another 100 lines or so)

    If you have any idea what I'm doing wrong, my blood pressure would greatly appreciate it.

    ReplyDelete
    Replies
    1. andrnev26 October 2015 at 01:38

      Hi Could you do the following.

      Open a command prompt and just type "path" and post the output here.

      Delete
  2. Unknown28 December 2016 at 09:12

    I've tried your method baut I get this message for X64:

    install for x64

    * installing to library 'C:/Users/IM3CEF8N/Documents/R/win-library/3.0'
    * installing *source* package 'ROracle' ...
    ** package 'ROracle' correctement décompressé et sommes MD5 vérifiées
    Oracle Client Shared Library 64-bit - 11.2.0.1.0 Operating in ORACLE_HOME en
    nment.
    found Oracle Client C:\Oraclex64\PC_DEMO\product\11.2.0\client_1\BIN
    found Oracle Client include C:\Oraclex64\PC_DEMO\product\11.2.0\client_1\oci
    lude
    copying from C:\Oraclex64\PC_DEMO\product\11.2.0\client_1\oci\include
    ** libs
    gcc -m64 -I"C:/PROGRA~1/R/R-30~1.2/include" -DNDEBUG -I./oci -I"d:/RCompi
    RANpkg/extralibs64/local/include" -O2 -Wall -std=gnu99 -mtune=core2 -c
    i.c -o rodbi.o
    rodbi.c:1:0: sorry, unimplemented: 64-bit mode not compiled in
    /* Copyright (c) 2011, 2016, Oracle and/or its affiliates.
    ^
    make: *** [rodbi.o] Error 1
    ERROR: compilation failed for package 'ROracle'
    * removing 'C:/Users/IM3CEF8N/Documents/R/win-library/3.0/ROracle'
    * restoring previous 'C:/Users/IM3CEF8N/Documents/R/win-library/3.0/ROracle'

    ReplyDelete
  3. andrnev28 December 2016 at 21:33

    Can you post the output of running the "path" command?

    ReplyDelete
  4. Unknown1 March 2017 at 07:43

    Hello. I followed the instructions, it does seem as if it installed both versions x86 and x64 on my Windows 7 machine. See below.
    C:\Program Files\R\R-3.3.2\bin>R CMD INSTALL --build --merge-multiarch ROracle_1
    .2-1.zip

    install for i386

    * installing to library 'C:/Users/lp187q/Documents/R/win-library/3.3'
    package 'ROracle' successfully unpacked and MD5 sums checked

    install for x64

    * installing to library 'C:/Users/lp187q/Documents/R/win-library/3.3'
    package 'ROracle' successfully unpacked and MD5 sums checked

    C:\Program Files\R\R-3.3.2\bin>

    I also tried installing the 1.3.1 version on TAR.gz and ZIP forms and didnt work either.

    Despite it says it installed correctly, when I run library(ROracle) I get:
    > library(ROracle)
    Error: package ‘ROracle’ is not installed for 'arch = i386'

    I did check that my RStudio was set up for 32 bits too.

    My PC is 64 bits, but I only have installed the Oracle Full Client 32 bits (11.2.0.4)

    In case you need it: this is my path:
    PATH=E:\R\Rtools\bin;E:\R\Rtools\gcc-4.6.3\bin;C:\oracle\product\11.2.0_32\bin;C:\oracle\product\11.2.0_32\bin;C:\oracle\product\11.2.0_32\perl\bin;C:\Program Files (x86)\Intel\iCLS Client\;C:\Program Files\Intel\iCLS Client\;C:\Program Files\Common Files\Microsoft Shared\Microsoft Online Services;C:\Program Files (x86)\Common Files\Microsoft Shared\Microsoft Online Services;C:\Program Files (x86)\RSA SecurID Token Common;C:\Windows\system32;C:\Windows;C:\Windows\System32\Wbem;C:\Windows\System32\WindowsPowerShell\v1.0\;C:\Windows\System32\WindowsPowerShell\v1.0\;C:\Program Files (x86)\NVIDIA Corporation\PhysX\Common;C:\Program Files (x86)\Enterprise Vault\EVClient\;C:\Program Files\1E\NomadBranch\;C:\Program Files (x86)\Intel\Intel(R) Management Engine Components\DAL;C:\Program Files\Intel\Intel(R) Management Engine Components\DAL;C:\Program Files (x86)\Intel\Intel(R) Management Engine Components\IPT;C:\Program Files\Intel\Intel(R) Management Engine Components\IPT;C:\Program Files\Intel\WiFi\bin\;C:\Program Files\Common Files\Intel\WirelessCommon\;C:\Program Files\Git\cmd;C:\Users\lp187q\AppData\Local\Programs\Python\Python36\Scripts\;C:\Users\lp187q\AppData\Local\Programs\Python\Python36\;C:\Program Files\Intel\WiFi\bin\;C:\Program Files\Common Files\Intel\WirelessCommon\;C:\Program Files (x86)\Microsoft VS Code\bin;C:\oracle\product\11.2.0_32\NETWORK\ADMIN;;C:\oracle\product\11.2.0_64\NETWORK\ADMIN

    ReplyDelete
  5. Unknown24 October 2017 at 05:42

    on step 7th, it gives me this error "--merge-multiarch applies only to a single tarball". How can I solve this?

    ReplyDelete

Post a Comment

Popular posts from this blog

Basic Econometrics - Chapter 1 - Exercise 1

Exercise 1.1 Table 1.2 gives data on the Consumer Price Index (CPI) for seven industrialized countries with 1982-1984 = 100 as base of the index. a. From the given data, compute the inflation rate of each country. b. Plot the inflation rate for each country against time (i.e. use the horizantal axis for time and the vertical axis for the inflation rate) c. What broad conclusions can you draw abou the inflation experience in the seven countries? d. Which countries inflation seems to be most variable? Can you offer any explanation? ## Note here I have to skip several rows and add column names. Have a look at ## the raw data. Column names are c('Year', 'Canada', 'France', 'Germany', ## 'Italy','Japan', 'UK', 'US') cpi <- read.table("https://raw.githubusercontent.com/cablegui/Econometrics/master/OriginalData/Table%201.2.txt", skip = 6, col.names = c("Ye...
Post a Comment
Read more

Step by step guide to installing and using miktex with RStudio (Windows)

Using miktex with Rstudio is very easy with the miktex portable app available from http://miktex.org/portable. Steps 1. Follow the instructions from http://miktex.org/portable to download and unzip the miktex portable application in a loccation of your choice. 2. In R write the following code in a script and save it. Note that the E:\\Software-Silo\\Miktex\\miktex\\bin location is the location where I unzipped the miktex portable application. # Install miktex y <- Sys.getenv("PATH") x <- paste0(y,";","E:\\Software-Silo\\Miktex\\miktex\\bin") Sys.setenv(PATH=x) 3. Run Miktex by double clicking the following application "miktex-portable.cmd" in the Miktex main directory. 4. Run step 2 in RStudio to install the path into R environment. 5. Open a new RNW in RStudio to test whether Miktex works . 6. Run Compile PDF in RStudio. It should be just at the top of the RNW file created in step 5. 7. You will now see a PDF file whic...
3 comments
Read more