Using Oracle as a Backend to Rails

In a recent project I was asked to use Oracle as the database for a Rails application rather than MySQL. I am somewhat of an Oracle newbie and so setting this up was a bit of a learning experience. This note describes the steps that it took to accomplish the task.

For testing purposes I installed a copy of Oracle Database Express Edition (XE) on one of my servers. This is a free download from Oracle that offers much of the core functionality, albeit with some restrictions. I've written up the steps needed to Install Oracle XE on Fedora Core 5 Linux in a separate note. The following steps assume that you have this up and running.

My installation had the Rails application and web server on one machine and the Oracle database on another.

Oracle has its own way of handling client/server comunication over a network. For a Rails application to talk to a remote database it needs to access all that machinery and the preferred way of doing that in Rails is to use a Ruby interface to the Oracle Call Interface. This resides on the machine that hosts the Rails application.

So you need to install an Oracle client interface, the Ruby OCI interface and tell Rails how to connect to the database.

The Ruby OCI interface is called ruby-oci8 and comes in two variants, depending on the Oracle client software that you installed. In the Oracle/Linux installation note I installed the Full Client, however I ran into problems getting ruby-oci8 to work with the libraries included with that client (it works fine with the Server libraries). So I have to recommend the other route using the Oracle Instant Client.

1. Install Oracle Instant Client

You can download the Instant Client packages for free from Oracle, although you will need to create an account for yourself first. Pick your platform, agree to their license and you'll get a page full of packages in different versions. All you need are these two from Version 10.2:
Instant Client Package - Basic
instantclient-basic-linux-x86-64-10.2.0.3-20070103.zip (36 MB)
Instant Client Package - SDK
instantclient-sdk-linux-x86-64-10.2.0.3-20070103.zip (0.6 MB)
The ruby-oci8 install guide tells you to install these into /opt/oracle but that is probably not mandatory. I had downloaded the files into /proj/downloads.

# mkdir /opt/oracle
# cd /opt/oracle
# unzip /proj/downloads/instantclient-basic-linux-x86-64-10.2.0.3-20070103.zip
# unzip /proj/downloads/instantclient-sdk-linux-x86-64-10.2.0.3-20070103.zip
# cd instantclient_10_2

Then create the following symbolic link

# ln -s libclntsh.so.10.1 libclntsh.so

Edit /etc/bashrc to include the directory in your LD_LIBRARY_PATH environment variable by adding these lines

LD_LIBRARY_PATH=/opt/oracle/instantclient_10_2:$LD_LIBRARY_PATH
export LD_LIBRARY_PATH

The start a new shell, or source /etc/bashrc, in order to see the new value for the variable. Installing ruby-oci8 won't work without it!

2. Install ruby-oci8

Download version 1 of the interface from rubyforge:

# wget http://rubyforge.org/frs/download.php/16630/ruby-oci8-1.0.0-rc1.tar.gz

Unpack, make and install the package

# tar -zxvf ruby-oci8-1.0.0-rc1.tar.gz
# cd ruby-oci8-1.0.0-rc1
# make
# make install

The make calls ruby setup.rb to configure and then compile the code. It should all go smoothly provided the Oracle libraries are in place and it knows where to find them.

The official ruby-oci8 install guide may help if you have problems.

3. Test out the Connection to the Remote Database

This assumes that the remote database is running and that you have enable the test HR database that ships with the server. Test the connection with a simple Ruby one-liner. Note that this specifies the remote host using Oracle's Easy Connect naming scheme (bit like a URL). Alternatively you can set up a TNSNAMES.ORA files if you know about all that. Here the username is hr, the password is hr, the remote host is testbed.int.craic.com and the Oracle SID is XE. If the call is successful then you should get back a bunch of lines listing various jobs in the database.

# ruby -r oci8 -e "OCI8.new('hr', 'hr', '//testbed.int.craic.com/XE').exec(
     'select * from jobs') do |r| puts r.join(','); end"
AD_PRES,President,20000,40000
AD_VP,Administration Vice President,15000,30000
AD_ASST,Administration Assistant,3000,6000
FI_MGR,Finance Manager,8200,16000
[...]

If that works then you have finished the installation and can now move on to creating your Rails application. If, like me, you are not very familiar with setting up a database in Oracle here are some steps to get you started.

4. Create a 'Database' in Oracle

First of all, Oracle does not use that term in the way you might expect if you are coming from MySQL!

Oracle has an instance, identified by an SID (which in the above case is XE). Within that instance you need to create a new User and in doing so you create a new tablespace (I think that is the right term) which is equivalent to creating a database in MySQL.

You need to access the Oracle Server web site on your server, login as system and create the new user. Use the name of your Rails app for convenience. I use myapp in this example. Enter a password and check the boxes Connect and Resource in the Roles section. Don't worry about the Directly Granted System Privileges. Then click Alter User to set it up.

5. Create Your Rails Application

Back to you client machine. Assuming that you have Rails already setup, go to your target directory and create the subdirectories for your app.

# cd /proj/rails
# rails myapp

Then configure your database.yml file. You will actually want three Oracle users, one for the development, production and test databases, but just consider the development version right now. A suitable block for that file would be:

development:
adapter: oci
#  database: myapp_development
host: testbed.int.craic.com/XE
username: myapp
password: myapp

Note that the database: key is not relevant for Oracle, as I mentioned. The hostname is this Oracle-specific combination of the real hostname followed by a slash and the SID (which is always XE for Oracle Express Edition)

(Update: you can create your rails app with this option:

# rails myapp --database=oracle

Which specifies the adapter to be oracle instead of oci, but when doing so I got an error telling me that the TNS service name was not properly specified. But if you use the oci adapter as above it should work fine.)

From here on out you are working in regular Rails. You can create and modify your tables using migrations, you can CRUD your data and everything should just work.

If you are coming from MySQL, like me, then be aware that Oracle uses different underlying data types than MySQL and it appears that can be an issue with regard to dates and times in certain cases. Don't use the datatype :tinyint as it will barf - use :integer, :limit => 4 instead.

Note that in Rails 1.2.1 there is a bug that, with Oracle, will put the text empty_clob() in to empty textareas in a form. This has been noted and fixed but is not yet released. A simple work around is to set that field explicitly to blank string in the new action in your controller. For example:

def new
@blog_entry = BlogEntry.new
@blog_entry.text = ''
end