CODIEZ

second-person plural present subjunctive of coder

Setup Oracle Instant Client and Ruby Oci8 Gem on Mac

Recently i have had to get my dev environment setup to connect to oracle for a project.

Getting the client setup can sometimes be a real pain with a Mac. Oracle does provide client libraries but they are painfully slow in updating them and fixing bugs. There was an outstanding segmentation fault bug for over 2 years.

Here are the steps to get it setup on Mac, and it would probably work for ubuntu etc, just have not tested it.

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:

1
2
3
4
5
6
7
8
9
10

cd ~/Downloads
#basic
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

1
2
3
4
5
6

cd instantclient_11_2
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

1
2
3
4
5
6
7
8
9
10
11
12

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

1
2

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

Put in your tnsnames, example:

tnsnames.ora 
1
2
3
4
5
6
7
8
9

 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:

.oracle_client 
1
2
3
4
5
6

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:

1
2

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

1
2
3
4
5
6
7
8
9
10
11
12

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 (i.e like when oracle does not fix a bug for 2 years! )

Install Ruby oci gem

1
2
3
4
5
6

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…

1
2
3
4
5
6
7
8

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.

Comments