Export R results to Oracle Database

R is a programming language, so there is no limit for the user to use procedure which is only found in standard packages. R is object-oriented and have a lot of very useful libraries developed by contributor.

R. (source: www.coursera.org)

RODBC is an R package that implements ODBC database connectivity.

R> install.packages("RODBC")
R> library(RODBC)
R> con <- odbcConnect("DD1", uid="rquser", pwd="rquser", rows_at_time = 500)
R> sqlSave(con, test_table, "TEST_TABLE")
R> sqlQuery(con, "select count(*) from TEST_TABLE")
R> d <- sqlQuery(con, "select * from TEST_TABLE")
R> close(con)

RJDBC is an implementation of the R DBI package – database interface – that uses JDBC as the back-end connection to the database. Any database that supports a JDBC driver can be used in connection with RJDBC.

R> install.packages("RJDBC")
R> library(RJDBC)
R> drv <- JDBC("oracle.jdbc.OracleDriver",
classPath="…tklocal/instantclient_11_2/ojdbc5.jar", " ")
R> con <- dbConnect(drv, " jdbc:oracle:thin:@myHost:1521:db", "rquser", "rquser")
R> dbWriteTable(con, "TEST_TABLE", test_table)
R> dbGetQuery(con, "select count(*) from TEST_TABLE")
R> d <- dbReadTable(con, "TEST_TABLE")
R> dbDisconnect(con)

ROracle is an implementation of the R DBI package that uses Oracle OCI for high performance and scalability with Oracle Databases. It requires Oracle Instant Client or Oracle Database Client to be installed on the client machine.

Download the instant client for your OS Platform from http://www.oracle.com/technetwork/database/features/instant-client/index-097480.html

You need to download
– Instant Client package – Basic
– Instant Client package – SDK

R> install.packages("ROracle")
R> library(ROracle)
R> drv <- dbDriver("Oracle")
R> con <- dbConnect(drv, "rquser", "rquser")
R> dbWriteTable(con,”TEST_TABLE”, test_table)
R> dbGetQuery(con, "select count(*) from TEST_TABLE")
R> d <- dbReadTable(con, "TEST_TABLE")
R> dbDisconnect(con)