I was trying to connect to Oracle database today and had a few problems along the way. For most of the problems, a quick search came up with fixes. One problem, however, I had trouble finding a solution for. The error I was getting was:

Error: *** Exception PhusionPassenger::UnknownError in 
PhusionPassenger::Railz::ApplicationSpawner (Error while trying to 
retrieve text for error ORA-12154 (OCIError)) (process 666):
from env.c:257:in oci8lib.so

So, I was thinking I might post what I did as it might be helpful for others.

Assumption: Ruby, rails, mod_rails (passenger) and Oracle instantclient are already installed.

1. Download the ruby-oci8 library from http://rubyforge.org/projects/ruby-oci8/
Then compile it:

cd ~/Downloads/ruby-oci8-1.0.7
ruby setup.rb config
make
sudo make install

2. My Instantclient for Oracle is located in /Library/Oracle. If yours is in a different location, all the following instances of “/Library/Oracle/” should be changed according to your location

cd /Library/Oracle/instantclient_10_2
sudo ln -s libclntsh.dylib.10.1 libclntsh.dylib
sudo ln -s libocci.dylib.10.1 libocci.dylib

3. In ~/.bash_profile add:

export ORACLE_HOME=/Library/Oracle/instantclient_10_2
export TNS_ADMIN=$ORACLE_HOME
export LD_LIBRARY_PATH=$ORACLE_HOME
export DYLD_LIBRARY_PATH=$ORACLE_HOME
export SQLPATH=$ORACLE_HOME

I also did the following:

sudo mkdir -p /b/32_216/rdbms
sudo ln -s /Library/Oracle/instantclient_10_2 /b/32_216/rdbms/lib

4. Install the oracle-adapter gem:

sudo gem install activerecord-oracle-adapter --source http://gems.rubyonrails.org

or

gem sources -a http://gems.rubyonrails.org
sudo gem install activerecord-oracle-adapter

I then tested for connectivity by running:

ruby /usr/bin/irb

In the IRb console, I typed:

require 'oci8'

And got

true

Also, within the app dir, starting console had no problems connecting to the Oracle database. This suggested that the problem was not in the installation of Oracle but that Passenger did not find the Oracle libraries, which lead to checking the environment files.

5. If /Oracle/instantclient_10_2/network/admin/tnsnames.ora does not exist, run:

mkdir -p /Oracle/instantclient_10_2/network/admin
touch /Oracle/instantclient_10_2/network/admin/tnsnames.ora

Then open /Oracle/instantclient_10_2/network/admin/tnsnames.ora and add the following (you will need to change the <var> according to your server and database details):

<addressname> =
 (DESCRIPTION =
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(Host = <hostname>)(Port = <port>))
   )
 (CONNECT_DATA =
   (SERVICE_NAME = <sid>)
 )
)

The tnsnames.ora file contains client side network configuration parameters.

6. I am using the passenger preferences pane, therefore:

cd /etc/apache2/passenger_pane_vhosts

Open the appropriate app.local.vhost.conf file and add:

SetEnv ORACLE_HOME "/Library/Oracle/instantclient_10_2"
SetEnv LD_LIBRARY_PATH "/Library/Oracle/instantclient_10_2"

for example:

<VirtualHost *:80>
  ServerName app.local
  SetEnv ORACLE_HOME "/Library/Oracle/instantclient_10_2"
  SetEnv LD_LIBRARY_PATH "/Library/Oracle/instantclient_10_2"
  DocumentRoot "/Users/username/where/app/is/public"
  RailsEnv development
  <Directory "/Users/username/where/app/is/public">
    Order allow,deny
    Allow from all
  </Directory>
</VirtualHost>

7. Restart your server by either running

sudo apachectl -k graceful

or within your rails application dir:

touch tmp/restart.txt

And you should be good to go… or at least that what worked for me :)

Other resources:

1. Connecting Ruby on Rails to Oracle on an Intel Mac in Leopard, take 2
2. How to setup Ruby and new Oracle Instant Client on Leopard
3. Connecting to Oracle from Ruby on Rails