环境变量
library(ROracle)
# 编码设置,否则会出现中文乱码
Sys.setenv(NLS_LANG = "Simplified Chinese_china.AL32UTF8")
# 时区设置,否则读写时间类型数据时可能报错
Sys.setenv(TZ = "PRC")
Sys.setenv(ORA_SDTZ = "PRC")
建立连接
host <- 'xx.xx.xx.xx'
SERVICE_NAME <- 'xxx'
INSTANCE_NAME <- 'xxx'
port <- 1521
drv <- dbDriver("Oracle")
connect.string <- paste(
"(DESCRIPTION=",
"(ADDRESS=(PROTOCOL=tcp)(HOST=", host, ")(PORT=", port, "))",
"(CONNECT_DATA=(SERVICE_NAME=", SERVICE_NAME,
")(INSTANCE_NAME=", INSTANCE_NAME,")))",
sep = ""
)
key <- dbConnect(drv, username = "user", password = "yourpw", dbname = connect.string)
基本操作
读
res <- dbSendQuery(key, "select ...") %>% fetch()
写
dbWriteTable(key, 'tablename', thedata, append = T)
断开
dbDisconnect(key)