Getting your dev environment setup to connect to an Oracle DB can sometimes be a real pain. This tip covers setting up the client, and installing the ruby oci8 gem.

First grab the 64 bit client from:

http://www.oracle.com/technetwork/topics/intel-macsoft-096467.html

You need:

• instantclient-basic-macos.x64-11.2.0.3.0.zip
• instantclient-sqlplus-macos.x64-11.2.0.3.0.zip
• instantclient-sdk-macos.x64-11.2.0.3.0.zip

You can also grab the lite version, if you do not need any translations.

Unzip the files:

unzip -qq instantclient-basic-macos.x64-11.2.0.3.0.zip

#lite
unzip -qq instantclient-basiclite-macos.x64-11.2.0.3.0.zip

unzip -qq instantclient-sqlplus-macos.x64-11.2.0.3.0.zip 

unzip -qq instantclient-sdk-macos.x64-11.2.0.3.0.zip

Move files

mkdir -p /usr/local/oracle/product/instantclient_64/11.2.0.3.0/bin
mkdir -p /usr/local/oracle/product/instantclient_64/11.2.0.3.0/lib
mkdir -p /usr/local/oracle/product/instantclient_64/11.2.0.3.0/jdbc/lib
mkdir -p /usr/local/oracle/product/instantclient_64/11.2.0.3.0/rdbms/jlib
mkdir -p /usr/local/oracle/product/instantclient_64/11.2.0.3.0/sqlplus/admin

and..

mv ojdbc* /usr/local/oracle/product/instantclient_64/11.2.0.3.0/jdbc/lib/
mv x*.jar /usr/local/oracle/product/instantclient_64/11.2.0.3.0/rdbms/jlib/

# rename glogin.sql to login.sql
mv glogin.sql /usr/local/oracle/product/instantclient_64/11.2.0.3.0/sqlplus/admin/login.sql

# Move lib & sdk
mv *dylib* /usr/local/oracle/product/instantclient_64/11.2.0.3.0/lib/
mv sdk /usr/local/oracle/product/instantclient_64/11.2.0.3.0/lib/sdk

mv *README /usr/local/oracle/product/instantclient_64/11.2.0.3.0/
mv * /usr/local/oracle/product/instantclient_64/11.2.0.3.0/bin/

Setup TNS Names

mkdir -p /usr/local/oracle/admin/network
touch /usr/local/oracle/admin/network/tnsnames.ora

Put in your tnsnames, example:

 ORADEMO=
 (description=
   (address_list=
     (address = (protocol = TCP)(host = 127.0.0.1)(port = 1521))
   )
 (connect_data =
   (service_name=orademo)
 )
)

Setup your environment

Create a file to store your oracle client environment variables with touch ~/.oracle_client

Add the following to it:

export ORACLE_BASE=/usr/local/oracle
export ORACLE_HOME=$ORACLE_BASE/product/instantclient_64/11.2.0.3.0
export PATH=$ORACLE_HOME/bin:$PATH
export DYLD_LIBRARY_PATH=$ORACLE_HOME/lib:$DYLD_LIBRARY_PATH
export TNS_ADMIN=$ORACLE_BASE/admin/network
export SQLPATH=$ORACLE_HOME/sqlplus/admin

Then run:

echo "source ~/.oracle_client" >> ~/.bash_profile
source ~/.bash_profile

Which will add the environment variables to your .bash_profile, You can also find this in my dotfiles

Test Sql*Plus works

sqlplus user/pass@orademo

SQL*Plus: Release 11.2.0.3.0 Production on Thu Sep 12 09:19:55 2013

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select table_name from user_tables;

You could also store multiple versions of the client, with different .oracle_client files, with a small shell script you could switch between different versions.

Install Ruby oci gem

cd /usr/local/oracle/product/instantclient_64/11.2.0.3.0/lib
ln -s libclntsh.dylib.11.1 libclntsh.dylib
ln -s libocci.dylib.11.1 libocci.dylib

# Make sure sdk directory is in /usr/local/oracle/product/instantclient_64/11.2.0.3.0/lib
gem install ruby-oci8

Test that it works...

irb
irb(main):001:0> require 'oci8'
irb(main):006:0> o = OCI8.new('user','pass','127.0.0.1/orademo')
=> #<OCI8:user>
irb(main):011:0> o.exec('select * from dual') do |r| puts r.join(','); end
X
=> 1
irb(main):012:0> exit

If you have issues connecting with an SID or Service Name, try using the IP.

You now have a working oracle database client, and can connect to it from ruby.