R 连接 Oracle

一、windows
1、安装java
2、下载并解压 instantclient-basic-win32-11.2.0.1.0
3、
方式一 RODBC
library(RODBC)
#本地连接
channel <- odbcConnect("DSN_xuefliang",uid="system",pwd="52332580")
tb_zyf <- sqlQuery(channel,'SELECT * FROM t')
odbcClose(channel)

#不需要设置ODBC
dbconnection <- odbcDriverConnect("Driver=Oracle in OraDB12Home1;
                                  Server=127.0.0.1; Database=orcl;
                                  uid=system; pwd=52332580")

#远程连接
#下载instantclient-odbc-windows.x64-12.1.0.2.0.zip
#1、odbc配置TNS service name 10.112.0.6:1521/orcl
channel <- odbcConnect("dsn",uid="gssvac",pwd="gssvac")
tb_zyf <- sqlQuery(channel,'SELECT * FROM sys_log')
odbcClose(channel)


tb_zyf <- sqlQuery(dbconnection,'SELECT * FROM t')
odbcClose(dbconnection)

方式二 RJDBC
Sys.setenv(JAVA_HOME='C:\\Program Files\\Java\\jre1.8.0_66')
library(RJDBC)
#本地连接
drv <- JDBC("oracle.jdbc.OracleDriver",
            classPath="C:\\app\\Admin\\product\\instantclient_11_2\\ojdbc5.jar", " ")
con <- dbConnect(drv, "jdbc:oracle:thin:@127.0.0.1:1521:orcl", "system", "52332580")
#dbWriteTable(con, "TEST_TABLE", t)
dbGetQuery(con, "select * from T")
dbDisconnect(con)

#远程连接
drv <- JDBC("oracle.jdbc.OracleDriver",
            classPath="C:\\app\\Admin\\product\\instantclient_11_2\\ojdbc5.jar", " ")
con <- dbConnect(drv, "jdbc:oracle:thin:@10.112.0.6:1521:orcl", "gssvac", "gssvac")
log <- dbGetQuery(con, "select * from sys_log")
names(log)
head(log$USER_NAME)
dbDisconnect(con)

方式三 ROracle
library(ROracle)
drv <- dbDriver("Oracle")
connect.string <- paste(
  "(DESCRIPTION=",
  "(ADDRESS=(PROTOCOL=tcp)(HOST=10.112.0.6)(PORT=1521))",
  "(CONNECT_DATA=(SERVICE_NAME=orcl)))", sep = "")
#疫苗
con <- dbConnect(drv, username = "gssvac", password = "gssvac",
                 dbname = connect.string)
depart <- dbGetQuery(con, "select * from sys_log")
dbDisconnect(con)

#金卫信
con <- dbConnect(drv, username = "vaccuser", password = "gscdc20140516",
                 dbname = connect.string)

depart <- dbGetQuery(con, "select * from department")
child <- dbGetQuery(con, "select count(CHIL_NAME) from child")
inoculation <- dbGetQuery(con,"select * from inoculation")
bacterin <- dbGetQuery(con,"select * from bacterin")
dbDisconnect(con)

二、linux
方式一 RJDBC
1、下载instantclient_12_1，并解压。
2、
library(RJDBC)
drv <- JDBC("oracle.jdbc.OracleDriver",
            classPath="/home/xuefliang/Downloads/instantclient_12_1/ojdbc6.jar", " ")
#疫苗
#服务器：管理工具-高级Window防火墙-入站规则-新建规则-端口：1521
con <- dbConnect(drv, "jdbc:oracle:thin:@10.112.0.6:1521:orcl", "gssvac", "gssvac")
log <- dbGetQuery(con, "select * from sys_log")
dbDisconnect(con)

#金卫信
con <- dbConnect(drv, "jdbc:oracle:thin:@10.112.0.6:1521:orcl", "vaccuser", "gscdc20140516")
depart <- dbGetQuery(con, "select * from department")
dbDisconnect(con)

方式二 ROracle
Sys.setenv(ORACLE_HOME='/usr/lib/oracle/12.1/client64')
#疫苗
#select userenv("language") from dual; 查询编码集
Sys.setenv(NLS_LANG="SIMPLIFIED CHINESE_CHINA.ZHS16GBK")
library(ROracle)
drv <- dbDriver("Oracle")
connect.string <- paste(
  "(DESCRIPTION=",
  "(ADDRESS=(PROTOCOL=tcp)(HOST=10.112.0.6)(PORT=1521))",
  "(CONNECT_DATA=(SERVICE_NAME=orcl)))", sep = "")
con <- dbConnect(drv, username = "gssvac", password = "gssvac",
                 dbname = connect.string)

log <- dbGetQuery(con, "select * from sys_log")
log$USER_NAME <- iconv(log$USER_NAME,from="GBK",to="UTF-8")
log$REGION_NAME <- iconv(log$REGION_NAME,from="GBK",to="UTF-8")
dbDisconnect(con)

#金卫信
Sys.setenv(NLS_LANG="AMERICAN_AMERICAN.ZHS16GBK")
library(ROracle)
drv <- dbDriver("Oracle")
connect.string <- paste(
  "(DESCRIPTION=",
  "(ADDRESS=(PROTOCOL=tcp)(HOST=10.112.0.6)(PORT=1521))",
  "(CONNECT_DATA=(SERVICE_NAME=orcl)))", sep = "")
con <- dbConnect(drv, username = "vaccuser", password = "gscdc20140516",
                 dbname = connect.string)

depart <- dbGetQuery(con, "select * from department")
depart$DEPA_NAME <- iconv(depart$DEPA_NAME,from="GBK",to="UTF-8")
dbDisconnect(con)