最近由于需要通过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
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