r访问oracle数据库,R学习笔记之<访问远程Oracle数据库>

一、install the Oracle Instant Client

注意：Each step must be performed as root.

oracle-instantclient12.1-basic-12.1.0.2.0-1.x86_64.rpm (必须的)

oracle-instantclient12.1-odbc-12.1.0.2.0-1.x86_64.rpm

oracle-instantclient12.1-sqlplus-12.1.0.2.0-1.x86_64.rpm

oracle-instantclient12.1-devel-12.1.0.2.0-1.x86_64.rpm

oracle-instantclient12.1-jdbc-12.1.0.2.0-1.x86_64.rpm

(2) Install the packages.

[root@localhost ~]# rpm -ivh oracle-instantclient12.1-*

(3) 在/etc/ld.so.conf.d目录下创建一个配置文件

oracle-instant-client.conf 用来将Oracle client libraries 添加到

LD_LIBRARY_PATH

[root@localhost ~]# cat > /etc/ld.so.conf.d/oracle-instant-client.conf << EOL

> /usr/lib/oracle/12.1/client64/lib

> EOL

这样编辑的好处是输入目录时可以用Tab键补全。

Rebuild the LD_LIBRARY_PATH：

[root@localhost ~]# ldconfig

(4)使用sqlplus连接数据库，如果你使用RPMs来安装client，则它们已经存在你的PATH中了。(运行之前确保网络没问题)

[root@localhost ~]# sqlplus64 username/password@remoteIP:1521/databaseName

SQL*Plus: Release 12.1.0.2.0 Production on Tue Dec 15 22:58:53 2015

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

sqlplus登录命令常用的几种方式：

sqlplus username/password  如：普通用户登录  sqlplus scott/tiger

sqlplus username/password@net_service_name 如: sqlplus scott/tiger@orcl

sqlplus  username/password as sysdba 如：sqlplus sys/admin as sysdba

sqlplus username/password@//host:port/sid

以上步骤均通过后则第一步成功。

二、Connecting to an Oracle Database with an ODBC Driver

注意：首先要确保安装了unixODBC包

(1)Configure the Oracle ODBC Driver. odbcinst.ini 必须被包含在/etc目录下

[root@localhost ~]# vi /etc/odbcinst.ini将以下内容添加到末尾

[Oracle 12g ODBC driver]

Description = Oracle ODBC driver for Oracle 12g

Driver = /usr/lib/oracle/12.1/client64/lib/libsqora.so.12.1

Setup =

FileUsage =

CPTimeout =

CPReuse =

Driver Logging = 7

[ODBC]

Trace = Yes

TraceFile = /tmp/odbc.log

ForceTrace = Yes

Pooling = No

DEBUG = 1

(2)Run ldd to verify that driver dependencies are installed and working.

[root@localhost ~]# ldd /usr/lib/oracle/12.1/client64/lib/libsqora.so.12.1

ldd: warning: you do not have execution permission for `/usr/lib/oracle/12.1/client64/lib/libsqora.so.12.1'

linux-vdso.so.1 => (0x00007fffa24f9000)

libdl.so.2 => /lib64/libdl.so.2 (0x00007f7890f18000)

libm.so.6 => /lib64/libm.so.6 (0x00007f7890c16000)

libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f78909f9000)

libnsl.so.1 => /lib64/libnsl.so.1 (0x00007f78907e0000)

librt.so.1 => /lib64/librt.so.1 (0x00007f78905d8000)

libclntsh.so.12.1 => /usr/lib/oracle/12.1/client64/lib/libclntsh.so.12.1 (0x00007f788d61a000)

libodbcinst.so.2 => /lib64/libodbcinst.so.2 (0x00007f788d408000)

libc.so.6 => /lib64/libc.so.6 (0x00007f788d047000)

/lib64/ld-linux-x86-64.so.2 (0x00007f78915a1000)

libmql1.so => /usr/lib/oracle/12.1/client64/lib/libmql1.so (0x00007f788cdd0000)

libipc1.so => /usr/lib/oracle/12.1/client64/lib/libipc1.so (0x00007f788ca52000)

libnnz12.so => /usr/lib/oracle/12.1/client64/lib/libnnz12.so (0x00007f788c348000)

libons.so => /usr/lib/oracle/12.1/client64/lib/libons.so (0x00007f788c102000)

libaio.so.1 => /lib64/libaio.so.1 (0x00007f788bf00000)

libclntshcore.so.12.1 => /usr/lib/oracle/12.1/client64/lib/libclntshcore.so.12.1 (0x00007f788b98e000)

libltdl.so.7 => /lib64/libltdl.so.7 (0x00007f788b783000)

注意：这里报了一个warning： you do not have execution permission for `/usr/lib/oracle/12.1/client64/lib/libsqora.so.12.1'",需要运行下列命令：

[root@localhost ~]# chmod 755 /usr/lib/oracle/12.1/client64/lib/lib*

再次运行ldd，此时不会有warning了。

(3)在/etc目录下创建odbc.ini，并写入以下内容

[DSN]

Application Attributes = T

Attributes = W

BatchAutocommitMode = IfAllSuccessful

BindAsFLOAT = F

CloseCursor = F

DisableDPM = F

DisableMTS = T

Driver = Oracle 12g ODBC driver

DSN = DSN

EXECSchemaOpt =

EXECSyntax = T

Failover = T

FailoverDelay = 10

FailoverRetryCount = 10

FetchBufferSize = 64000

ForceWCHAR = F

Lobs = T

Longs = T

MaxLargeData = 0

MetadataIdDefault = F

QueryTimeout = T

ResultSets = T

ServerName = caihua

SQLGetData extensions = F

Translation DLL =

Translation Option = 0

DisableRULEHint = T

UserID = username

Password = password

StatementCache=F

CacheBufferSize=20

UseOCIDescribeAny=F

MaxTokenSize=8192

上面需要改的有Driver,ServerName,DSN,UserID,Password.

注意点：最开始的

[DSN]非常重要,可以改成其他的，比如

[ODBC],但必须要有,这是数据源的名称Data Source Name，对应于R中odbcConnect("DSN",uid=..,pwd=..),ServerName对应于tnsnames.ora的开头部分。UserID和Password可以为空

(4) 创建一个目录/etc/oracle,专门放tnsnames.ora,编写tnsnames.ora,内容为

caihua =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(Host = hostIP )(Port = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = orcl)

)

)

[root@localhost ~]# mkdir /etc/oracle

[root@localhost ~]# cd /etc/oracle

[root@localhost oracle]# vi tnsnames.ora

可调用

sqlplus username/password@net_service_name 来判断tnsnames.ora是否正确

其中net_service_name对应的是tnsnames.ora的第一个单词，即caihua，也对应odbc.ini的ServerName。

(5)在/etc/profile.d目录下创建一个配置脚本oracle-instant-client.sh，用来被系统启动时调用，这个脚本主要设置ORACLE_HOME, TWO_TASK,  LD_LIBRARY_PATH 和 TNS_ADMIN环境变量。

# Set ORACLE_HOME to the directory where the bin and lib directories are located for the oracle client

export ORACLE_HOME=/usr/lib/oracle/12.1/client64

# No need to add ORACLE_HOME to the linker search path. oracle-instant-client.conf in

# /etc/ld.so.conf.d should already contain /usr/lib/oracle/12.1/client64.

# Alternately, you can set it here by uncommenting the following line:

export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib

# Define the default location where Oracle should look for the server

export TWO_TASK=//remotehostIP:1521/listener

# Define where to find the tnsnames.ora file

export TNS_ADMIN=/etc/oracle

remotehostIP：远程数据库的ip地址

(6)Source the oracle-instant-client.sh script 并确保每个环境变量都正确配置

[root@localhost oracle]# source /etc/profile.d/oracle-instant-client.sh

[root@localhost oracle]# echo $ORACLE_HOME

/usr/lib/oracle/12.1/client64

[root@localhost oracle]# echo $LD_LIBRARY_PATH

:/usr/lib/oracle/12.1/client64/lib

[root@localhost oracle]# echo $TNS_ADMIN

/etc/oracle

(7) 确保 Oracle ODBC driver 正常工作

isql -v 数据源

[root@localhost oracle]# isql -v DSN

+---------------------------------------+

| Connected!                            |

|                                       |

| sql-statement                         |

| help [tablename]                      |

| quit                                  |

|                                       |

+---------------------------------------+

SQL>

通过以上完成了第二步。

三、R中加载RODBC访问数据库

1.安装R

yum install libXt-devel

yum install readline-devel

yum install gcc

yum install gcc-c++

yum install gcc-gfortran

tar -zxvf R-3.2.3.tar.gz

cd R-3.2.3

./configure

make

更改环境变量：

先看一下/etc/profile和/etc/profile.d/的对比：两个文件都是设置环境变量文件的，/etc/profile是永久性的环境变量,是全局变量，/etc/profile.d/设置所有用户生效；/etc/profile.d/比/etc/profile好维护，不想要什么变量直接删除/etc/profile.d/下对应的shell脚本即可，不用像/etc/profile需要改动此文件

在/etc/profile.d/目录下创建一个文件R.sh，加入以下内容

export R_HOME= R_Home_Path

export PATH=$PATH:$R_HOME/bin

然后运行下面命令

source /etc/profile.d/R.sh

2.下载RODBC包

首先要下载unixODBC、unixODBC-devel包

install.packages("RODBC",dependencies=TRUE,repos="http://cran.mirror.com")

3.加载RODBC包

library(RODBC)

4.创建连接

channel