Configure macOS Catalina to connect to Oracle DB using RStudio and odbc library

00_explanation.md

Setting up RStudio for Oracle pull using odbc on maxOS Catalina

1. install unixodbc using homebrew

You can install homebrew here. Once that is done, run this in the terminal.

brew install unixodbc

2. Download instant client from Oracle

instantclient-basic-macos.x64-19.3.0.0.0dbru.zip
instantclient-sqlplus-macos.x64-19.3.0.0.0dbru.zip
instantclient-sdk-macos.x64-19.3.0.0.0dbru.zip
instantclient-jdbc-macos.x64-19.3.0.0.0dbru.zip
instantclient-odbc-macos.x64-19.3.0.0.0dbru.zip

3. Set up .bash_profile

# Oracle instant client variables
export ORACLE_HOME=/usr/local/oracle/instantclient  
export OCI_HOME=$ORACLE_HOME  
export OCI_LIB_DIR=$OCI_HOME  
export OCI_INCLUDE_DIR=$OCI_HOME/sdk/include  
export NLS_LANG=AMERICAN_AMERICA.UTF8  
export DYLD_LIBRARY_PATH=$OCI_LIB_DIR
export PATH=$ORACLE_HOME:$PATH

4. Set up the odbc.ini file. You can find the location of the odbc.ini file by running odbcinst -j. It will return something like this

unixODBC 2.3.7
DRIVERS............: /usr/local/etc/odbcinst.ini
SYSTEM DATA SOURCES: /usr/local/etc/odbc.ini
FILE DATA SOURCES..: /usr/local/etc/ODBCDataSources
USER DATA SOURCES..: /Users/matt/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

Edit the file /usr/local/etc/odbc.ini to look like

[oracle]
AggregateSQLType=FLOAT
Application Attributes=T
Attributes=W
BatchAutocommitMode=IfAllSuccessful
BindAsFLOAT=F
CacheBufferSize=20
CloseCursor=F
DisableDPM=F
DisableMTS=T
DisableRULEHint=T
Driver=/usr/local/oracle/instantclient/libsqora.dylib.19.1
DSN=oracle
EXECSchemaOpt=
EXECSyntax=T
Failover=T
FailoverDelay=10
FailoverRetryCount=10
FetchBufferSize=64000
ForceWCHAR=F
LobPrefetchSize=8192
Lobs=T
Longs=T
MaxLargeData=0
MaxTokenSize=8192
MetadataIdDefault=F
QueryTimeout=T
ResultSets=T
ServerName=****
SQLGetData extensions=F
SQLTranslateErrors=F
StatementCache=F
Translation DLL=
Translation Option=0
UseOCIDescribeAny=F
UserID = ****
Password = ****

5. Copy 01_setup.sh into the downloads file. This should be the same place where you downloaded the instant client files. Here I am assuming you copied 01_setup.sh into a file called setup.sh in the Downloads folder. To run the file type

sudo ./setup.sh

Make sure you run this in the downloads folder!

6. Open RStudio

7. Make a new RScript with 04_connection_object.r. Once you install the packages odbc and dbi you can delete the two install commands. When you run this script you should not return any errors.

8. In RStudio make a new sql file using 05_pull.sql. If this is the first time you have used the preview feature, RStudio will need to download a package. It will prompt you for this. When you save the file pull.sql, RStudio will show the result. It should return the current time. If you click the preview button, RStudio will rerun the pull and the time should update. If it does, you are all set.

01_setup.sh

#!/bin/bash

# move all the files into one
cp -a ~/Downloads/instantclient_19_3/.   ~/Downloads/instantclient
cp -a ~/Downloads/instantclient_19_3-2/. ~/Downloads/instantclient
cp -a ~/Downloads/instantclient_19_3-3/. ~/Downloads/instantclient
cp -a ~/Downloads/instantclient_19_3-4/. ~/Downloads/instantclient
cp -a ~/Downloads/instantclient_19_3-5/. ~/Downloads/instantclient
# delete all the files you copied
rm -rf instantclient_19_3 instantclient_19_3-1 instantclient_19_3-2 instantclient_19_3-3 instantclient_19_3-4 instantclient_19_3-5
# change all the permissions so that you can alter the file attibutes
chmod u+w ~/Downloads/instantclient/*
# remove the apple quarentine attributes
xattr -r -d -s com.apple.quarantine instantclient
mkdir /usr/local/oracle
mv ~/Downloads/instantclient /usr/local/oracle
# sym link instant client files into /usr/local/lib
ln -s /usr/local/oracle/instantclient/* /usr/local/lib
# sym link unixodbc files into instant client. The unixodbc files are symlinked into /usr/local/lib by homebrew
ln -s /usr/local/Cellar/unixodbc/2.3.7/lib/* /usr/local/oracle/instantclient
# Change the relative paths to absolute paths
install_name_tool -id "/usr/local/oracle/instantclient/libsqora.dylib.19.1" /usr/local/oracle/instantclient/libsqora.dylib.19.1 
install_name_tool -change "@rpath/libclntsh.dylib.19.1" "/usr/local/oracle/instantclient/libclntsh.dylib.19.1" /usr/local/oracle/instantclient/libsqora.dylib.19.1 
install_name_tool -change "@rpath/libodbcinst.2.dylib" "/usr/local/oracle/instantclient/libodbcinst.2.dylib" /usr/local/oracle/instantclient/libsqora.dylib.19.1 
install_name_tool -rpath "@executable_path/../../rdbms/lib" "/usr/local/lib" /usr/local/oracle/instantclient/libsqora.dylib.19.1
install_name_tool -rpath "@executable_path/../../ldap/lib" "/usr/local/lib" /usr/local/oracle/instantclient/libsqora.dylib.19.1
install_name_tool -rpath "@executable_path/../../opmn/lib" "/usr/local/lib" /usr/local/oracle/instantclient/libsqora.dylib.19.1

02_odbc.ini

[oracle]
AggregateSQLType=FLOAT
Application Attributes=T
Attributes=W
BatchAutocommitMode=IfAllSuccessful
BindAsFLOAT=F
CacheBufferSize=20
CloseCursor=F
DisableDPM=F
DisableMTS=T
DisableRULEHint=T
Driver=/usr/local/oracle/instantclient/libsqora.dylib.19.1
DSN=banner
EXECSchemaOpt=
EXECSyntax=T
Failover=T
FailoverDelay=10
FailoverRetryCount=10
FetchBufferSize=64000
ForceWCHAR=F
LobPrefetchSize=8192
Lobs=T
Longs=T
MaxLargeData=0
MaxTokenSize=8192
MetadataIdDefault=F
QueryTimeout=T
ResultSets=T
ServerName=****
SQLGetData extensions=F
SQLTranslateErrors=F
StatementCache=F
Translation DLL=
Translation Option=0
UseOCIDescribeAny=F
UserID = ****
Password = ****

03_add_to_bash_profile

# Oracle instant client stuff
export ORACLE_HOME=/usr/local/oracle/instantclient  
export OCI_HOME=$ORACLE_HOME  
export OCI_LIB_DIR=$OCI_HOME  
export OCI_INCLUDE_DIR=$OCI_HOME/sdk/include  
export NLS_LANG=AMERICAN_AMERICA.UTF8  
export DYLD_LIBRARY_PATH=$OCI_LIB_DIR
export PATH=$ORACLE_HOME:$PATH

04_connection_object.R

install.packages('odbc')
install.packages('DBI')
library(DBI)
library(odbc)
con <- DBI::dbConnect(odbc::odbc(), 'oracle')

05_pull.sql

-- !preview conn=con

select sysdate from dual