The latest upgrade to OS X has proved a little fiddly for users of ruby. Any ruby gem that contains native code needs reinstalling so that this code can be recompiled to 64bit. You might think after doing that you are done and dusted…. but don’t forget your OCI8 library if you are using Oracle with your rails stack!

If you can’t start rails because of the following error then you will most likely need to recompile OCI8:

1
2
Missing these required gems:
  activerecord-oracle_enhanced-adapter

Now I’m assuming you installed the ruby OCI8 library manually, as I detailed in my post on getting oracle and rails to play together. Try firing up irb and seeing if you can require oci8:

1
2
irb(main):002:0> require 'oci8'
LoadError: no such file to load -- oci8lib

There’s your problem – we need to reinstall the OCI8 library. To save you looking back to my previous post I’ve just copied the instructions below (and altered them slightly as a new version of the library is now out).

Step 1: Download ruby-oci8

Download the latest tarball from the ruby-oci8 rubyforge page to your ~/Downloads directory. At the time of writing the latest is version 1.0.6.

Step 2: Make and install ruby-oci8

If you’ve followed the instructions from part 1 then you should have your oracle instant client installed in /opt/oracle/instantclient. If you’ve not installed the instant client yet then scoot off to part 1 and perform those steps before going any further. If you’ve installed it somewhere different then you will need to amend the following commands where appropriate:

1
2
3
4
5
6
7
cd ~/Downloads
tar xvzf ruby-oci8-1.0.6.tar.gz

cd ruby-oci8-1.0.6
ruby setup.rb config -- --with-instant-client=/opt/oracle/instantclient
make
sudo make install

Step 3: Test it

1
2
irb(main):001:0> require 'oci8'
=> true

You should now be able to use rubygems to load the oracle_enhanced adapter:

1
2
irb(main):002:0> gem 'activerecord-oracle_enhanced-adapter'
=> true

Fingers crossed your rails app should also work now.

Just recently I’ve had to do a lot of work getting rails to play with a 15 year old Oracle database. Needless to say this database doesn’t follow the conventions we’ve all grown used to with rails. The table names are all over the place, the primary keys aren’t surrogate keys but actually have business meaning, composite keys which include date columns, crazy methods of generating primary keys instead of sequences….. if there’s a rails convention this thing doesn’t break then I’ve not found it yet!

However rails does provide methods for us to get it working with these legacy systems. Of course, if we were designing an application from scratch, we’d never need these techniques because we’d do it “the rails way” from the start. So I’m going to cover a couple of techniques which I’ve had to use in case you find yourself with the misfortune of having to marry rails to a pig!

Telling rails to use a different table and primary key

If we have a model named Student then rails convention dictates this model will map to a table called students and it will have a primary key called id. Well the real world isn’t always so accommodating. No problem…

1
2
3
4
class Student < ActiveRecord::Base
  set_table_name :student
  set_primary_key :s_stno
end

Composite keys

In this instance, the composite primary keys gem is your friend. Simply install…

1
sudo gem install composite_primary_keys

... and require in your environment.rb...

1
require 'composite_primary_keys'

Now you are ready to start supplying multiple keys in your models…

1
2
3
4
class CourseFee < ActiveRecord::Base
  set_table_name "course_avail"
  set_primary_keys :course_code, :course_date
end

You may notice that one of those composite keys is a date. This can cause you problems when you try and generate a url for an instance of this class because the date won’t be output in the correct format. So we need to override the to_param method to output the date in database format.

1
2
3
  def to_param
    "#{course_code},#{course_date.to_s(:db)}"
  end

Non standard primary key generation

This one was tricky! Now it wouldn’t be too hard if your legacy system uses a sequence to generate primary keys because you can just tell rails to use this sequence if it’s not named using the rails convention (which is tablename_seq).

1
2
3
class Student < ActiveRecord::Base
  set_sequence_name 'a_non_standard_sequence_name'
end

However you could find yourself in a nasty situation where the database doesn’t use a sequence for primary keys – it uses a value stored in a table instead. No problem you’re thinking to yourself, I’ll just use a before_create hook to populate the primary key? Nope, not going to work. The oracle_enhanced adapter will still go off and try to use a sequence called tablename_seq causing the whole house of cards to come crashing down. So we need to stop oracle_enhanced from looking for this sequence and instead tell it to generate the primary key some other way.

I stole the basis of this hack from the oracle_enhanced website and added my own twist to it to fit my circumstances. I doubt your scenario will exactly match mine (and I hope for your sake it doesn’t!), but you can use this as a starting point.

So…. the primary keys in my evil system are stored in a table called counter. There is only one row in this table and a column for each table you want a primary key for. Nasty. So I started off by generating a model for this table with a method to pull out a primary key…

1
2
3
4
5
6
7
8
9
10
class Counter < ActiveRecord::Base
  set_table_name :counter

  def self.next_primary_key(col)
    current_value = Counter.first.read_attribute(col)
    new_value = current_value + 1
    Counter.connection.execute("UPDATE counter SET #{col} = #{new_value}")
    new_value
  end
end

Now assuming there is a column in the counter table called student, I can pull a primary key out by calling:

1
Counter.next_primary_key('student')

This will increment the value in the column and return it. It’s a nasty way to generate primary keys but hey, I didn’t design this!

So now we can access these primary keys in a tidy way we need to get our Student model to use it for its primary keys. And this is where the fun starts – we need to monkey patch the oracle_enhanced adapter to use this table, but only when our table doesn’t have a sequence. To do this we create an initialiser in config/initializers.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
# config/initializers/oracle_enhanced.rb

ActiveRecord::ConnectionAdapters::OracleEnhancedAdapter.class_eval do
  alias_method :orig_next_sequence_value, :next_sequence_value

  def next_sequence_value(sequence_name)
    if sequence_name =~ /^counter-/
      # Strip the counter- part out of the string and pass the remainder to next_primary_key
      Counter.next_primary_key(sequence_name.match("counter-(.*$)")[1])
    else
      orig_next_sequence_value(sequence_name)
    end
  end
end

What this code does is check what the name of the model’s sequence is and if it starts with counter- it looks in the counter table for the primary key. So when we specify the following sequence name…

1
2
3
4
5
class Student < ActiveRecord::Base
  set_table_name :student
  set_primary_key :s_stno
  set_sequence_name 'counter-student'
end

... our little monkey patch will spot the counter- prefix, strip it out and make its primary key the result of a call to the following:

1
Counter.next_primary_key('student')

Perfect! It’s a little complicated but we’ve now got a flexible solution to cater for whatever crazy method of primary key generation our predecessors may have dreamt up. And the best part is it gracefully falls back to the default behaviour if the sequence name doesn’t start with counter-.

As I said before, the main guts of this hack (the monkey patching of oracle_enhanced) was written by Raimonds Simanovskis on the oracle_enhanced website. I merely souped it up a little with some regular expressions and adopted it to fit my particular use case.

Further reading

If you are wondering how to setup rails to talk to an Oracle database then you might be interested in my previous two part series on the subject which starts with this post

Ok, so this post is a bit earlier than advertised, but it’s better than being late! Following up from Rails on Oracle: Part 1 – Installing the oracle instant client on Mac OS X, now we are going to get rails to use our freshly installed oracle client…

Step 1: Download ruby-oci8

Download the latest tarball from the ruby-oci8 rubyforge page to your ~/Downloads directory. At the time of writing the latest is version 1.0.5.

Step 2: Make and install ruby-oci8

If you’ve followed the instructions from part 1 then you should have your oracle instant client installed in /opt/oracle/instantclient. If you’ve not installed the instant client yet then scoot off to part 1 and perform those steps before going any further. If you’ve installed it somewhere different then you will need to amend the following commands where appropriate:

1
2
3
4
5
6
7
cd ~/Downloads
tar xvzf ruby-oci8-1.0.5.tar.gz

cd ruby-oci8-1.0.5
ruby setup.rb config -- --with-instant-client=/opt/oracle/instantclient
make
sudo make install

Step 3: Install the oracle_enhanced adapter

1
sudo gem install activerecord-oracle_enhanced-adapter

Step 4: Configure you rails project to use an oracle database

Lets say with have the same oracle database as we had in part 1 which looked like so:

  • Host : myserver
  • domain : mydomain.com
  • Port : 1521
  • SID : mydatabase
  • Service : mydatabase.mydomain.com

We’d express this in our database.yml file with the following entry:

1
2
3
4
5
development:
  adapter: oracle_enhanced
  database: myserver:1521/mydatabase.mydomain.com
  username: myusername
  password: mypassword

Step 5: Profit!

Now (fingers crossed!) you should be ready. It’s been tricky for me to test this thoroughly because I don’t have access to a clean mac to start from scratch with. My mac is already set up and ready to go so it’s possible I could have missed something. If you have problems then leave a comment and I’ll try my best to sort you out and and update the instructions.

Looking for part 1?

Part 1 : Instructions on how to install the Oracle instant client on OS X and test it with sqlplus.

These instructions were performed using version 10.5.6 of OS X and the 10.1.03 Oracle instant client.

Update: I’ve just performed these steps with version 11.2.0.3.0 (64-bit) of the instant client and it worked without a hitch.

Step 1: Download the instant client from Oracle

We’re going to use the instant client because this is by far the easiest way to get up and running. Traditionally oracle clients have been quite heavy weight but the instant client is quick to download and easy to install.

Go to the appropriate download page for your CPU:

There are several download options. Download the following to your ~/Downloads directory:

  • Instant Client Package – Basic
  • Instant Client Package – SQL*Plus
  • Instant Client Package – SDK

Step 2: Install the files on your machine

I usually install these files in the /opt/oracle/instantclient directory. Assuming you’ve downloaded the zip files to your ~/Downloads directory we need to make a new home for our instant client and unzip the files there….

1
2
3
4
5
6
7
8
9
mkdir -p /opt/oracle/
cd /opt/oracle

mv ~/Downloads/instantclient*.zip .
unzip instantclient-basic-macosx-10.2.0.4.0.zip
unzip instantclient-sdk-macosx-10.2.0.4.0.zip
unzip instantclient-sqlplus-macosx-10.2.0.4.0.zip

mv instantclient_10_2 instantclient

Obviously if you have a slightly different version of the client you are going to need to amend those paths slightly.

Step 3: Create some symbolic links for the libraries

1
2
3
cd instantclient
ln -s libclntsh.dylib.10.1 libclntsh.dylib
ln -s libocci.dylib.10.1 libocci.dylib

Step 4: Set your ORACLE_HOME, NLS_LANG and DYLD_LIBRARY_PATH variables

You could do this in a couple of ways. Because I’m the only user of my machine I like to do this in my personal ~/.bash_profile. If you share the machine with someone who also wants to use the client you may want to put this in /etc/profile or/etc/bashrc. The choice is yours. I find ~/.bash_profile is cleaner:

1
2
3
export NLS_LANG="AMERICAN_AMERICA.UTF8"
export ORACLE_HOME=/opt/oracle/instantclient
export DYLD_LIBRARY_PATH=$ORACLE_HOME

Now you could close your terminal session and start a new one to force a reload or try this instead:

1
source ~/.bash_profile

Do a quick echo on $ORACLE_HOME and $DYLD_LIBRARY_PATH to make sure these environment variables are set correctly.

Step 5: Put sqlplus in your $PATH

There are couple of ways to do this. You could add /opt/oracle/bin to your $PATH or you could add a sym link to the sqlplus binary in a directory which is already in your $PATH. I’m going to do the latter as I don’t want to make my $PATH variable longer just for one executable.

I’ve already got /usr/local/bin in my $PATH so I’m going to make the sym link there:

1
ln -s /opt/oracle/instantclient/sqlplus /usr/local/bin/sqlplus

Just give that a test to make sure it’s getting found :

1
which sqlplus

This should return /usr/local/bin/sqlplus. Check your $PATH variable if it’s not working.

Step 6: Try it out!

Lets assume we’ve got the following Oracle database:

  • Host : myserver
  • domain : mydomain.com
  • Port : 1521
  • SID : mydatabase
  • Service : mydatabase.mydomain.com

And a username and password of :

  • Username : myuser
  • Password : mypassword

We can connect to this database using the following connect string:

1
sqlplus myuser/mypassword@//myserver:1521/mydatabase.mydomain.com

It’s a bit of a mouthful, but if that worked then we’re all up and running. In this instance we’re connecting over the default port of 1521. Because of this we can actually leave the port out and Oracle will assume the default:

1
sqlplus myuser/mypassword@//myserver/mydatabase.mydomain.com

Possible errors

ORA-12154: TNS:could not resolve the connect identifier specified

It couldn’t find your host. Try the following:

  • Instead of just putting myserver, try myserver.mydomain.com
  • Are you sure you got the port right? Oracle defaults to listening on 1521. Is the listener running on your server for sure? Try running lsnrctl status on the server just to double check it’s running and which port it’s on.

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

It’s found the host and the listener but it’s can’t find the database you’ve specified at that listener. The last part of the connect string where it says mydatabase.mydomain.com is where it’s falling over now. Again, the quickest way to check this is to ssh onto your oracle database server and query the listener with lsnrctl status which will also tell you which services the listener is listening on. Copy and paste the correct service into the sqlplus connect string and it should work.

ORA-01017: invalid username/password; logon denied

I think this one speaks for itself! If you’re sure of the username and password then maybe it contains characters which may need escaping? Try surrounding the username and password in double quotes:

1
sqlplus "myuser"/"mypassword"@//myserver:1521/mydatabase.mydomain.com

Coming in Part 2

Now that we can connect our machine to Oracle, in part 2 we’re going to get rails in on the fun as well. Check back in a week or so and I should have that post finished.

Update: Part 2 has now been published and shows you how to install the ruby-oci8 library, how to install the oracle_enhanced adapter and how to configure your database.yml.


Update 15/05/09: Added NLS_LANG to the environment variables