R ROracle install, test ( 설치 및 테스트 )

R에서 Oracle에 접속할 수 있는 패키지로 ROracle을 사용해 보자.

우선 사이트의 매뉴얼에 따라 Oracle Instant Client를 설치한다.

아마 sdk는 컴파일할 때 필요하나 보다 일단 받았다.

다운로드 완료 후 C:\instantclient_11_2에 다운 받은 압축파일을 모두 해제한다.

 

시스템 PATH에 추가 및 시스템변수에 OCI_LIB64=C:\instantclient_11_2, 추가한다.

혹시 모르니 Registry 컴퓨터\HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE 경로에 아래 내용을 추가했다.

 

 

Rstudio에서 install을 시도하니 오류가 발생한다.

R 3.5.1에 호환성 문제가 있을지 모르겠다.

> install.packages("ROracle")
Installing package into ‘C:/Users/username/Documents/R/win-library/3.5’
(as ‘lib’ is unspecified)
Package which is only available in source form, and may need compilation of C/C++/Fortran:
  ‘ROracle’
  These will not be installed

사이트를 확인해 보니 3.5용은 아직 준비가 안되어 있다.

https://cran.r-project.org/web/packages/ROracle/index.html

 

오라클에서 제공하는 ROracle을 다운로드 한다.

https://www.oracle.com/technetwork/database/database-technologies/r/roracle/downloads/index.html

 

다운로드 기본경로를 E:\ROracle로 하기 위해 디렉토리를 작성하고,

다운로드한 ROracle_1.3-1.zip을 E:\ROracle 경로에서 확인한다.

> setwd("E://ROracle")
> install.packages("ROracle_1.3-1.zip", repos=NULL)
Installing package into ‘C:/Users/username/Documents/R/win-library/3.5’
(as ‘lib’ is unspecified)
package ‘ROracle’ successfully unpacked and MD5 sums checked
> library("ROracle")
필요한 패키지를 로딩중입니다: DBI
Error: package or namespace load failed for ‘ROracle’:
 package ‘ROracle’ was installed by an R version with different internals; it needs to be reinstalled for use with this R version

 

가볍게 R버전이랑 호환이 안된다고 오류 발생.

 

소스빌드로 설치하려면 Rtools가 필요했다.Rtools35.exe를 설치하고,

시스템변수에 OCI_INC=C:\instantclient_11_2\sdk\include 추가,

소스는 https://cran.r-project.org/web/packages/ROracle/index.html에서 다운로드한다.

설치 매뉴얼에 나온대로 아래와 같이 실행하니 설치가 되었다.....허무하다..

D:\install>R CMD INSTALL ROracle_1.3-1.tar.gz
In R CMD INSTALL
* installing to library 'C:/Users/username/Documents/R/win-library/3.5'
* installing *source* package 'ROracle' ...
**  'ROracle'  , MD5 sums
Oracle Client Shared Library 64-bit - 11.2.0.4.0 Operating in Instant C
lient mode.
found Oracle Client C:\instantclient_11_2
found Oracle Client include C:\instantclient_11_2\sdk\include
copying from C:\instantclient_11_2\sdk\include
** libs
: this package has a non-empty 'configure.win' file,
so building only the main architecture

c:/Rtools/mingw_64/bin/gcc  -I"C:/PROGRA~1/R/R-35~1.1/include" -DNDEBUG
 -I./oci         -O2 -Wall  -std=gnu99 -mtune=generic -c rodbi.c -o rod
bi.o
c:/Rtools/mingw_64/bin/gcc  -I"C:/PROGRA~1/R/R-35~1.1/include" -DNDEBUG
 -I./oci         -O2 -Wall  -std=gnu99 -mtune=generic -c rooci.c -o roo
ci.o
c:/Rtools/mingw_64/bin/gcc -shared -s -static-libgcc -o ROracle.dll tmp
.def rodbi.o rooci.o C:\instantclient_11_2/oci.dll -LC:/PROGRA~1/R/R-35
~1.1/bin/x64 -lR
installing to C:/Users/username/Documents/R/win-library/3.5/ROracle/libs/
x64
** R
** inst
** byte-compile and prepare package for lazy loading
** help
*** installing help indices
  converting help for package 'ROracle'
    finding HTML links ...
    ExtDriver-class                         html
    OraConnection-class                     html
    OraDriver-class                         html
    OraResult-class                         html
    Oracle                                  html
Rd warning: C:/Users/username/AppData/Local/Temp/Rtmpgfr0Ob/R.INSTALL2900
145a4d5e/ROracle/man/Oracle.Rd:85: file link 'dbCommit' in package 'DBI
' does not exist and so has been treated as a topic
Rd warning: C:/Users/username/AppData/Local/Temp/Rtmpgfr0Ob/R.INSTALL2900
145a4d5e/ROracle/man/Oracle.Rd:85: file link 'dbRollback' in package 'D
BI' does not exist and so has been treated as a topic
Rd warning: C:/Users/username/AppData/Local/Temp/Rtmpgfr0Ob/R.INSTALL2900
145a4d5e/ROracle/man/Oracle.Rd:106: file link 'dbUnloadDriver' in packa
ge 'DBI' does not exist and so has been treated as a topic
Rd warning: C:/Users/username/AppData/Local/Temp/Rtmpgfr0Ob/R.INSTALL2900
145a4d5e/ROracle/man/Oracle.Rd:128: file link 'dbCommit' in package 'DB
I' does not exist and so has been treated as a topic
Rd warning: C:/Users/username/AppData/Local/Temp/Rtmpgfr0Ob/R.INSTALL2900
145a4d5e/ROracle/man/Oracle.Rd:129: file link 'dbRollback' in package '
DBI' does not exist and so has been treated as a topic
Rd warning: C:/Users/username/AppData/Local/Temp/Rtmpgfr0Ob/R.INSTALL2900
145a4d5e/ROracle/man/Oracle.Rd:133: file link 'fetch' in package 'DBI'
does not exist and so has been treated as a topic
    dbCommit-methods                        html
Rd warning: C:/Users/username/AppData/Local/Temp/Rtmpgfr0Ob/R.INSTALL2900
145a4d5e/ROracle/man/dbCommit-methods.Rd:54: file link 'fetch' in packa
ge 'DBI' does not exist and so has been treated as a topic
Rd warning: C:/Users/username/AppData/Local/Temp/Rtmpgfr0Ob/R.INSTALL2900
145a4d5e/ROracle/man/dbCommit-methods.Rd:55: file link 'dbCommit' in pa
ckage 'DBI' does not exist and so has been treated as a topic
    dbConnect-methods                       html
Rd warning: C:/Users/username/AppData/Local/Temp/Rtmpgfr0Ob/R.INSTALL2900
145a4d5e/ROracle/man/dbConnect-methods.Rd:165: file link 'fetch' in pac
kage 'DBI' does not exist and so has been treated as a topic
Rd warning: C:/Users/username/AppData/Local/Temp/Rtmpgfr0Ob/R.INSTALL2900
145a4d5e/ROracle/man/dbConnect-methods.Rd:166: file link 'dbCommit' in
package 'DBI' does not exist and so has been treated as a topic
    dbDriver-methods                        html
Rd warning: C:/Users/username/AppData/Local/Temp/Rtmpgfr0Ob/R.INSTALL2900
145a4d5e/ROracle/man/dbDriver-methods.Rd:88: file link 'fetch' in packa
ge 'DBI' does not exist and so has been treated as a topic
Rd warning: C:/Users/username/AppData/Local/Temp/Rtmpgfr0Ob/R.INSTALL2900
145a4d5e/ROracle/man/dbDriver-methods.Rd:89: file link 'dbCommit' in pa
ckage 'DBI' does not exist and so has been treated as a topic
    dbGetInfo-methods                       html
Rd warning: C:/Users/username/AppData/Local/Temp/Rtmpgfr0Ob/R.INSTALL2900
145a4d5e/ROracle/man/dbGetInfo-methods.Rd:279: file link 'fetch' in pac
kage 'DBI' does not exist and so has been treated as a topic
Rd warning: C:/Users/username/AppData/Local/Temp/Rtmpgfr0Ob/R.INSTALL2900
145a4d5e/ROracle/man/dbGetInfo-methods.Rd:280: file link 'dbCommit' in
package 'DBI' does not exist and so has been treated as a topic
    dbListConnections-methods               html
    dbReadTable-methods                     html
Rd warning: C:/Users/username/AppData/Local/Temp/Rtmpgfr0Ob/R.INSTALL2900
145a4d5e/ROracle/man/dbReadTable-methods.Rd:382: file link 'fetch' in p
ackage 'DBI' does not exist and so has been treated as a topic
Rd warning: C:/Users/username/AppData/Local/Temp/Rtmpgfr0Ob/R.INSTALL2900
145a4d5e/ROracle/man/dbReadTable-methods.Rd:383: file link 'dbCommit' i
n package 'DBI' does not exist and so has been treated as a topic
    dbSendQuery-methods                     html
Rd warning: C:/Users/username/AppData/Local/Temp/Rtmpgfr0Ob/R.INSTALL2900
145a4d5e/ROracle/man/dbSendQuery-methods.Rd:88: file link 'fetch' in pa
ckage 'DBI' does not exist and so has been treated as a topic
Rd warning: C:/Users/username/AppData/Local/Temp/Rtmpgfr0Ob/R.INSTALL2900145a4d5e/ROracle/man/dbSendQuer
y-methods.Rd:139: file link 'fetch' in package 'DBI' does not exist and so has been treated as a topic

Rd warning: C:/Users/username/AppData/Local/Temp/Rtmpgfr0Ob/R.INSTALL2900145a4d5e/ROracle/man/dbSendQuer
y-methods.Rd:140: file link 'dbCommit' in package 'DBI' does not exist and so has been treated as a to
pic
    fetch-methods                           html
Rd warning: C:/Users/username/AppData/Local/Temp/Rtmpgfr0Ob/R.INSTALL2900145a4d5e/ROracle/man/fetch-meth
ods.Rd:42: file link 'dbCommit' in package 'DBI' does not exist and so has been treated as a topic
    summary-methods                         html
Rd warning: C:/Users/username/AppData/Local/Temp/Rtmpgfr0Ob/R.INSTALL2900145a4d5e/ROracle/man/summary-me
thods.Rd:43: file link 'dbCommit' in package 'DBI' does not exist and so has been treated as a topic
** building package indices
** testing if installed package can be loaded

접속 및 SQL실행을 테스트한다.

> search()
 [1] ".GlobalEnv"        "package:ROracle"   "package:DBI"      
 [4] "tools:rstudio"     "package:stats"     "package:graphics" 
 [7] "package:grDevices" "package:utils"     "package:datasets" 
[10] "package:methods"   "Autoloads"         "package:base"   

> packageVersion("ROracle")
[1] ‘1.3.1’
> version$version.string
[1] "R version 3.5.1 (2018-07-02)"

> library("sqldf")
필요한 패키지를 로딩중입니다: gsubfn
필요한 패키지를 로딩중입니다: proto
필요한 패키지를 로딩중입니다: RSQLite

host <- "host"
port <- 1521
sid <- "SID"
connect.string <- paste(
  "(DESCRIPTION=",
  "(ADDRESS=(PROTOCOL=tcp)(HOST=", host, ")(PORT=", port, "))",
  "(CONNECT_DATA=(SID=", sid, ")))", sep = "")
#tnsnames.ora파일이 있는 경우 dbname=서비스명
> drv<-dbDriver("Oracle")
> conn<-dbConnect(drv,username="",password="",dbname=connect.string)
> rs<-dbSendQuery(conn,"select sysdate from dual")
> data<-fetch(rs)
> str(data)
'data.frame':	1 obs. of  1 variable:
 $ SYSDATE: POSIXct, format: "2018-11-29 19:10:36"
> sqldf("select * from data")
              SYSDATE
1 2018-11-29 19:10:36
> data<-NULL
> dbClearResult(rs)
[1] TRUE
> dbDisconnect(conn)
[1] TRUE
> dbUnloadDriver(drv)
[1] TRUE
> gc()
          used (Mb) gc trigger  (Mb) max used (Mb)
Ncells  939075 50.2    1934184 103.3  1193972 63.8
Vcells 1744015 13.4    8388608  64.0  2762222 21.1
> rm(list=ls())

ROracle_1.3-1_x64.zip

다운로드

다운로드 후 파일명을 ROracle_1.3-1.zip으로 변경 후 install.packages

 

사용법은 https://cran.r-project.org/web/packages/ROracle/ROracle.pdf 참고하자.