A New Post
Instant Client Downloads
for Microsoft Windows (x64) 64-bit
https://www.oracle.com/technetwork/topics/winx64soft-089540.html
title: “R Oracle” output: html_notebook —
SET OCI_INC=C:\Oracle_11G_ClientR2\product\11.2.0\client_1\oci\include SET OCI_LIB32=C:\Oracle_11G_ClientR2\product\11.2.0\client_1\BIN SET OCI_LIB64=C:\Oracle_11G_ClientR2\product\11.2.0\client_1\BIN https://github.com/cran/ROracle/blob/master/configure.win
Sys.getenv("TNS_ADMIN")
# Check system path
Sys.getenv()['PATH']
pkgbuild::find_rtools()
#devtools:::version_info
Sys.setenv(PATH = paste("D:/Rtools/bin", Sys.getenv("PATH"), sep=";"))
Sys.setenv(BINPREF = "D:/Rtools/mingw_$(WIN)/bin/")
Sys.setenv(OCI_LIB64="D:/app/oracle/app/oracle/instantclient_11_2/")
Sys.setenv(OCI_INC="D:/app/oracle/app/oracle/instantclient_11_2/sdk/include")
install.packages("DBI")
install.packages("ROracle", dep=T)
Sys.setenv(TNS_ADMIN = "D:/app/oracle/app/oracle/instantclient_11_2/admin")
https://stackoverflow.com/questions/31909695/connect-to-oracle-database-using-roracle-with-tnsnames-ora-on-mac-os-10-10 https://technology.amis.nl/2017/08/23/r-and-the-oracle-database-using-dplyr-dbplyr-with-roracle-on-windows-10/
library("DBI")
library("ROracle")
drv <- dbDriver("Oracle")
host <- "localhost"
port <- "1521"
# sid <- "orcl12c"
service <- "xe"
connect.string <- paste(
"(DESCRIPTION=",
"(ADDRESS=(PROTOCOL=tcp)(HOST=", host, ")(PORT=", port, "))",
"(CONNECT_DATA=(SERVICE_NAME=", service, ")))", sep = "")
# username = "system", password = "orcl"
con <- dbConnect(drv, username = "scott", password = "tiger", dbname = connect.string, prefetch = FALSE,
bulk_read = 1000L, stmt_cache = 0L, external_credentials = FALSE,
sysdba = FALSE)
dbReadTable(con, "EMP")
# install.packages("dplyr")
# install.packages("dbplyr")
library("DBI")
library("ROracle")
library("dplyr")
#below are required to make the translation done by dbplyr to SQL produce working Oracle SQL
sql_translate_env.OraConnection <- dbplyr:::sql_translate_env.Oracle
sql_select.OraConnection <- dbplyr:::sql_select.Oracle
sql_subquery.OraConnection <- dbplyr:::sql_subquery.Oracle
drv <- dbDriver("Oracle")
host <- "localhost"
port <- "1521"
sid <- "orcl12c"
connect.string <- paste(
"(DESCRIPTION=",
"(ADDRESS=(PROTOCOL=tcp)(HOST=", host, ")(PORT=", port, "))",
"(CONNECT_DATA=(SERVICE_NAME=", service, ")))", sep = "")
con <- dbConnect(drv, username = "scott", password = "tiger", dbname = connect.string, prefetch = FALSE,
bulk_read = 1000L, stmt_cache = 0L, external_credentials = FALSE,
sysdba = FALSE)
emp_db <- tbl(con, "EMP")
emp_db
# see example at http://www.oralytics.com/2015/05/loading-json-data-into-oracle-using.html
host <- "localhost"
port <- 1521
service <- "xe"
drv <- dbDriver("Oracle")
connect.string <- paste(
"(DESCRIPTION=",
"(ADDRESS=(PROTOCOL=tcp)(HOST=", host, ")(PORT=", port, "))",
"(CONNECT_DATA=(SERVICE_NAME=", service, ")))", sep = "")
con <- dbConnect(drv, username = "scott", password = "tiger", dbname = connect.string)
#dbWriteTable(con,"emp", test_table)
#dbGetQuery(con,"select count(*) from emp")
d <- dbReadTable(con, "emp")
dbDisconnect(con)
library(ROracle)
drv <- dbDriver("Oracle")
#con <- dbConnect(drv, "scott", "tiger")
# EZ connect string host:port/service
con <- dbConnect(drv = dbDriver("Oracle"), dbname = "DESKTOP-5A8MM7P:1521/xe", username = "scott", password = "tiger", prefetch = FALSE,
bulk_read = 1000L, stmt_cache = 0L, external_credentials = FALSE,
sysdba = FALSE)
#dbWriteTable(con,"emp", emp)
#dbGetQuery(con,"select * from scott.emp WHERE JOB = 'CLERK'")
dbReadTable(con, "emp")
dbDisconnect(con)