如何通过ROracle package通过r连接oracle数据库

最近由于需要通过oracle获取数据库数据，而本人也就会r语言所以一直在找方法。

由于本人对计算机系统方面知识浅薄，试遍了网上各种方法，总算亲测成功，分享给大家，希望帮助到有需要的人（其实主要还是自己老了记性不好，便于下次自己查看??）。

r语言连接oracle主要有三种方法，分别通过；RJDBC、RODBC和ROracle包。（主要是我就知道三种?）

RJDBC相对来说比较容易，安装好oracle instant client后添加path，然后就能安装成功，不过要连接的时候可能需要设置java路径，很大一串还要ojdbc6.jar文件，经常连的话挺麻烦的，所以一直希望通过ROracle连。

废话不多说了，直接上方法：

Install Oracle Instant Client

 1.  Download and unzip four files from oracle.com into instantclient_12_1 inside your Downloads folder:

instantclient-basic-macosx-12.1.zip
instantclient-sqlplus-macosx-12.1.zip
instantclient-sdk-macosx-12.1..zip
instantclient-jdbc-macosx-12.1.zip

2.  Create two symbolic links for the files that have the version appended. This is so the Ruby OCI8 driver can find what it’s looking for.

cd instantclient_12_1
ln -s libclntsh.dylib.12.1 libclntsh.dylib
ln -s libocci.dylib.12.1 libocci.dylib
cd ..

3.  Create directory /usr/local/oracle and then copy instantclient12 into it (assume your user name is xyz)

sudo mkdir /usr/local/oracle
sudo mv /users/xyz/Downloads/instantclient_12_1 /usr/local/oracle/instantclient12

4.  Open the system /etc/profile

sudo pico /etc/profile

Add the following lines to /etc/profile file at the end

ORACLE_HOME="/usr/local/oracle/instantclient12"
export ORACLE_HOME
DYLD_LIBRARY_PATH=$ORACLE_HOME:$DYLD_LIBRARY_PATH
export DYLD_LIBRARY_PATH
PATH=$ORACLE_HOME:$PATH
export PATH

Then save it and reload your profile

    source /etc/profile

 Test it out with the sqlplus command line app.

    sqlplus [username][/password]@//[hostname][:port][/serviceName]

        If you can connect with that, then you're good to go.

5.  (optional) Create network/admin folder inside instantclient12 and copy tnsnames.ora file into the folder:

        sudo mkdir /usr/local/oracle/instantclient12/network

        sudo mkdir /usr/local/oracle/instantclient12/network/admin

        sudo mv tnanames.ora /usr/local/oracle/instantclient12/network/admin

        chmod 755 /usr/local/oracle/instantclient12/network/admin/tnsnames.ora

Now we can use server alias such as cba11g to make connections. 

 

Install ROracle

1.  Open terminal and run the following commands to copy Oracle's library files 

sudo cp /usr/local/oracle/instantclient12/*.dylib.12.1 /usr/local/lib
sudo cp /usr/local/oracle/instantclient12/*.dylib /usr/local/lib

sudo cp /usr/local/oracle/instantclient12/*.dylib.12.1 /Library/Frameworks/R.framework/Resources/lib
sudo cp /usr/local/oracle/instantclient12/*.dylib /Library/Frameworks/R.framework/Resources/lib

2.  Download ROracle package file ROracle_1.2.2.tar.gz from https://cran.r-project.org/web/packages/ROracle/index.html and save it anywhere, say /users/xyz/Documents

3. Open R, and run command: install.packages("DBI")

4. Go back to terminal, run the following commands to install ROracle

sudo R CMD INSTALL --configure-args='--with-oci-lib=/usr/local/oracle/instantclient12 --with-oci-inc=/usr/local/oracle/instantclient12/sdk/include' /users/xyz/Documents/ROracle_1.2-2.tar.gz

5.  Now go to R and run command: library(ROracle)

写得还是蛮详细的相信大家还是都能看懂的，其中上面 Install Oracle Instant Client的第4部，添加路径，先

vim ~/.bash_profile 

添加路径，然后 source ~/.bash_profile

也是可以的。

最后附上方法来源：https://www.ecourse.org/news.asp?which=3233