getting ruby to talk to oracle on os x

Mon, Jul 6, 2009

JDBC works just fine and if you have OCI listening you can use C++ but wouldn’t it be nice to use Ruby to talk Oracle? Here’s how.

Download the Oracle Basic Instant Client and SDK from the Oracle Mac x86 page here. Unzip them to get the directory:

/Users/alistair/oracle/instantclient_10_2
Before you go any further, you need to set up the Oracle environment for building the Ruby driver and running the code. Add this to your ~/.profile
ORACLE_PATH=/Users/alistair/dev/oracle/ruby/instantclient_10_2
export DYLD_LIBRARY_PATH=“${ORACLE_PATH}”
export SQLPATH=“${ORACLE_PATH}”
export TNS_ADMIN=“${ORACLE_PATH}”
export NLS_LANG=“AMERICAN_AMERICA.UTF8”
export PATH=$PATH:$DYLD_LIBRARY_PATH
if you don’t, you’ll get this error when you run the code:
LoadError: dlopen(… ruby-1.9.1-p0/lib/ruby/site_ruby/1.9.1/i386-darwin9.6.3/oci8lib_191.bundle,
9): Library not loaded: /b/32_216/rdbms/lib/libclntsh.dylib.10.1
Referenced from: … lib/ruby/site_ruby/1.9.1/i386-darwin9.6.3/oci8lib_191.bundle
Reason: image not found - … lib/ruby/site_ruby/1.9.1/i386-darwin9.6.3/oci8lib_191.bundle
Now download the Ruby OCI8 client from here. Expand it to somewhere to get:
ruby-oci8-2.0.2
I used the 2.x branch as the 1.x oneĀ  doesn’t work with Ruby 1.9 and I want to use Ruby 1.9. The instructions are the same for the 1.x branch though:
cd ruby-oci8-2.0.2
make
make install
if you get an error along the lines of:
ruby-oci8-2.0.2/ext/oci8/oraconf.rb:866:in `get_home’: RuntimeError (RuntimeError)
it means your shell isn’t picking up the correct DYLD_LIBRARY_PATH that points to your Oracle instant client, so make sure that gubbins is in your ~/.profile as you’ll need it to run the code as well.

Once you’ve built and installed it and made sure the gubbins is in ~/.profile, here’s some code to talk to an Oracle database:

require ‘oci8’
db = OCI8.new(“USERNAME”, “PASSWORD”, “//SERVER_HOST_NAME[:PORT]/SID”)
sql = <<-EOS
select * from TABLE_NAME
EOS
number_of_rows = db.exec(sql) do |row|
  puts row.join(‘,’)
end
puts number_of_rows.to_s + ‘ rows were processed.’
and here’s the same code using DBI:
require ‘dbi’
db = DBI.connect(‘DBI:OCI8://SERVER_HOST_NAME[:PORT]/SID’, ‘USERNAME’, ‘PASSWORD’)
sql = “select * from TABLE_NAME”
statement = db.prepare(sql)
statement.execute
statement.fetch do |row|
  puts row.join”,”
end
statement.finish
db.disconnect if db

comments powered by Disqus