5

I have been trying to setup a connection to an Oracle database from R. Works great from Windows and Linux, but can't seem to get it to work on a Mac. Here is another article from which I followed the instructions with no luck. I would prefer to use the RODBC package, but am ok with using ROracle (I couldn't get either to work). I am tried the Oracle Instant Client and Actual Technologies drivers. Any help would be greatly appreciated.

Installing ROracle on Mac OS X - what is needed from Oracle?

Using the Active Technologies driver, I have setup and successfully tested the connection from the ODBC Manager, but odbcDataSources() doesn't return any of the connections I have defined.

The ROracle won't install due to a compilation error:

ld: library not found for -lclntsh
clang: error: linker command failed with exit code 1 (use -v to see invocation)
make: *** [ROracle.so] Error 1
ERROR: compilation failed for package ‘ROracle’
* removing ‘/Library/Frameworks/R.framework/Versions/3.1/Resources/library/ROracle’

Seems the instant client doesn't put files in the correct directories when you unzip them. Here is how I organized the files:

bin/adrci
bin/genezi
bin/sqlplus
bin/uidrvci
lib/libclntsh.dylib.11.1
lib/libnnz11.dylib
lib/libocci.dylib.11.1
lib/libociei.dylib
lib/libocijdbc11.dylib
lib/sqlplus.dylib
lib/sqlplusic.dylib
lib/odbc5.jar
lib/odbc6.jar
lib/xstream.jar
sdk/ - All the files from that ZIP file

I also had to create a rdbms/public directory. I put all the header files (.h) which got me further in the process.

Community
  • 1
  • 1
jbryer
  • 1,747
  • 3
  • 16
  • 29
  • Figured out piece of information, the `odbcDataSources()` function reads the connections from the `/usr/local/etc/odbc.ini` file. The ODBC Manager saves this file to `/Library/ODBC/odbc.ini` Copying allows me to see the available data sources. Connecting seems to work, and takes a while to execute a query, but no rows are returned. The structure of the data frame is correct though. – jbryer Sep 22 '14 at 19:42
  • I might be able to help, as I have both RODBC and ROracle running on my Mac (Mavericks). Have you made any progress with either? Which OS X version are you on? – joran Sep 30 '14 at 20:19
  • I ended up putting what wisdom I have [here](http://stackoverflow.com/q/26210317/324364). Perhaps it will help, some. – joran Oct 06 '14 at 15:08
  • Have you tried installing the MSODBC drivers? /usr/bin/ruby -e "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install)" brew tap microsoft/mssql-release https://github.com/Microsoft/homebrew-mssql-release brew update brew install --no-sandbox msodbcsql mssql-tools – Esben Eickhardt Jan 25 '18 at 10:24

3 Answers3

0

Maybe iODBC could do the job, you may take a look on this, most especially the Install part of the document. This page may also help you to setup iODBC.

If this method does not work, you may also try to use rstudio.

A. STEFANI
  • 6,707
  • 1
  • 23
  • 48
0

Been a while since I worked on a Mac...

Educated guess to resolve your compilation issue with ROracle:

Do export LD_LIBRARY_PATH=<instantclientlocation>/lib

Does that help?

Mark J. Bobak
  • 13,720
  • 6
  • 39
  • 67
0

I can help you with ROracle. Here are the steps to make it work:

  1. Install Oracle Instant Client (and reboot just in case)
  2. Get the Oracle R Distribution for your database (http://www.oracle.com/technetwork/database/database-technologies/r/r-distribution/downloads/index.html) and take note of the distribution version. In my example I use the latest, which currently is 1.5.1

Some notes:

R is pretty much a portable app. If you use it with Rstudio, you can easily switch from the RStudio settings between the OracleR and another distribution of R.

If you happen to have more than one distributions in your environmental variables, just make sure that you are using the Oracle R before you continue.

  1. Download and install the CLIENT and SUPPORTING ROracle packages for your Oracle R version (which we noted earlier) from here: http://www.oracle.com/technetwork/database/database-technologies/r/r-enterprise/downloads/index.html

There were some specifics to the versions, so here is the code from my working installation:

install.packages(".\\packages\\client\\ORE_1.5.1.zip",repos=NULL)
install.packages(".\\packages\\client\\OREbase_1.5.1.zip",repos=NULL)
install.packages(".\\packages\\client\\OREcommon_1.5.1.zip",repos=NULL)
install.packages(".\\packages\\client\\OREdm_1.5.1.zip",repos=NULL)
install.packages(".\\packages\\client\\OREeda_1.5.1.zip",repos=NULL)
install.packages(".\\packages\\client\\OREembed_1.5.1.zip",repos=NULL)
install.packages(".\\packages\\client\\OREgraphics_1.5.1.zip",repos=NULL)
install.packages(".\\packages\\client\\OREmodels_1.5.1.zip",repos=NULL)
install.packages(".\\packages\\client\\OREpredict_1.5.1.zip",repos=NULL)
install.packages(".\\packages\\client\\OREstats_1.5.1.zip",repos=NULL)
install.packages(".\\packages\\client\\ORExml_1.5.1.zip",repos=NULL)

install.packages(".\\packages\\supporting\\arules_1.5-0.zip",repos=NULL)
install.packages(".\\packages\\supporting\\Cairo_1.5-9.zip",repos=NULL)
install.packages(".\\packages\\supporting\\DBI_0.6-1.zip",repos=NULL)
install.packages(".\\packages\\supporting\\png_0.1-7.zip",repos=NULL)
install.packages(".\\packages\\supporting\\randomForest_4.6-12.zip",repos=NULL)
install.packages(".\\packages\\supporting\\ROracle_1.3-1.zip",repos=NULL)
install.packages(".\\packages\\supporting\\statmod_1.4.29.zip",repos=NULL)

require(DBI)
require(OREdm)
require(ORE)
require(ROracle)
ore.connect(user = "...", sid = "...", host = "...", password = "...")
ore.is.connected() #to test the connection

4. At this point you should be able to receive response from the server. If you cannot connect there are two things to do:

  1. Determine if the server recognizes the connection (If not, then I suppose that you should set-up the server with the SERVER Oracle R files. Still, I have not done this and cannot say how to do it.) http://www.oracle.com/technetwork/database/database-technologies/r/r-enterprise/downloads/index.html
  2. Determine if the server recognizes and refuses the connection (then you should check your ore.connect() parameters
Borislav Aymaliev
  • 803
  • 2
  • 9
  • 20