Last Updated: March 02, 2016
·
10.88K
· ismail

Setup Oracle instant client and ruby oci8 gem on Mac

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.

7 Responses
Add your response

Thank you so much!
"mkdir -p /usr/local/admin/network". should be "/usr/local/oracle/admin/network"

over 1 year ago ·

Thanks man, totally missed that. I have the correct config on my laptop but think when typing out the commands into the post i missed it.

over 1 year ago ·

Best tutorial I've found on this topic. Thank you. I've gotten the farthest with your notes. Should the dylib references not be in "bin" ? I'm getting the following error and would appreciate your guidance ...

/Users/doejohn/.rvm/rubies/ruby-2.0.0-p0/lib/ruby/siteruby/2.0.0/rubygems/coreext/kernelrequire.rb:45:in `require': dlopen(/Users/doejohn/.rvm/gems/ruby-2.0.0-p0/gems/ruby-oci8-2.1.6/lib/oci8lib200.bundle, 9): Library not loaded: /ade/b/2649109290/oracle/rdbms/lib/libclntsh.dylib.11.1 (LoadError)
Referenced from: /Users/doejohn/.rvm/gems/ruby-2.0.0-p0/gems/ruby-oci8-2.1.6/lib/oci8lib200.bundle
Reason: image not found - /Users/doejohn/.rvm/gems/ruby-2.0.0-p0/gems/ruby-oci8-2.1.6/lib/oci8lib
200.bundle
from /Users/doejohn/.rvm/rubies/ruby-2.0.0-p0/lib/ruby/siteruby/2.0.0/rubygems/coreext/kernelrequire.rb:45:in require' from /Users/doejohn/.rvm/gems/ruby-2.0.0-p0/gems/ruby-oci8-2.1.6/lib/oci8.rb:69:in<top (required)>'
from /Users/doejohn/.rvm/rubies/ruby-2.0.0-p0/lib/ruby/site
ruby/2.0.0/rubygems/coreext/kernelrequire.rb:110:in require' from /Users/doejohn/.rvm/rubies/ruby-2.0.0-p0/lib/ruby/site_ruby/2.0.0/rubygems/core_ext/kernel_require.rb:110:inrescue in require'
from /Users/doejohn/.rvm/rubies/ruby-2.0.0-p0/lib/ruby/siteruby/2.0.0/rubygems/coreext/kernel_require.rb:35:in require' from extract-test.rb:1:in<main>'

over 1 year ago ·

It looks like it cannot find dylib libraries.

This part in the post:

Move lib & sdk

mv dylib /usr/local/oracle/product/instantclient64/11.2.0.3.0/lib/
mv sdk /usr/local/oracle/product/instantclient
64/11.2.0.3.0/lib/sdk

I would check my dylib path with :

echo $DYLDLIBRARYPATH

and then remove the gem and try re-installing it if the paths are incorrect.

over 1 year ago ·

Also, your instructions are optimized for Mac, I presume, but what are your thoughts on the instructions @ the github project?

http://ruby-oci8.rubyforge.org/en/file.install-instant-client.html

over 1 year ago ·

Yes my instructions were done on a MAC the only change would be for Linux use LDLIBRARYPATH instead of $DYLDLIBRARYPATH

And the libs have the ext .so as mentioned in the install instructions

over 1 year ago ·

sqlplus "/ as sysdba" doesn't work on my mac.
i found out that that i have no listener. also there is no $ORACLE_HOME/bin/lsnrctl file (
Help please, what should i do to fix this problem

over 1 year ago ·