A collection of computer systems and programming tips that you may find useful.
Brought to you by Craic Computing LLC, a bioinformatics consulting company.

Tuesday, January 30, 2007

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- (36 MB)
Instant Client Package - SDK
instantclient-sdk-linux-x86-64- (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-
# unzip /proj/downloads/instantclient-sdk-linux-x86-64-
# 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
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_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:
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 = ''


Unknown said...

Hi Robert,
I'm also about to port an application developed in MySql over to Oracle. I'm not sure if I'll need to start all over again (not too big a job) or if there's a way of running the two databases within the same development environment (which I doubt 'cause of the differing naming conventions - does the migration :string equate to VARCHAR2, for example?).
I've also developed on Windows & the Oracle database will be on Sun Solaris - so your tips may be of use to me.


Raphaël Valyi said...

Many thanks for your Tutorial Robert,

This might sound obvious to most of your readers, but if it could save somebody's time, here is a trick:

if when typing make for ruby-oci8, you get something:
checking for load library path... (LD_LIBRARY_PATH)... /[..]/ruby-oci8-1.0.0-rc1/ext/oci8/extconf.rb:3: command not found: file /opt/oracle/instantclient_10_2/libclntsh.so.10.1
(ld.so.conf)... not found
checking for cc... ok
checking for gcc... yes
checking for LP64... no
checking for ruby header... ok
error messages:
Set the environment variable ORACLE_HOME if Oracle Full Client.
Append the path of Oracle client libraries to LD_LIBRARY_PATH if Oracle Instant Client.

That's probably you don't have the Unix 'file' command installed on your distro. Might sound obvious, but it took me some hours two times (after forgetting), so, now at least one can Google it hopefully.

Raphaël Valyi.

Jazzy Buddy said...

Just wanted to know if we can write models for the Oracle packages. Like, if I have an oracle package named "user_pkg", and a function "function_1" in it, can I have a model under the name "UserPkg" and then
access the function "function_1", in it.

If creating a model is not the right way, how to access those packages
in the models?


Blogger said...

If you need your ex-girlfriend or ex-boyfriend to come crawling back to you on their knees (even if they're dating somebody else now) you must watch this video
right away...

(VIDEO) Win your ex back with TEXT messages?

Archive of Tips