Ruby Oracle DBI ActiveRecord in 7 steps

Setting up ruby to work with Oracle seems to be a pain for a lot of people. Here are the steps I follow to set it up on a linux box from nothing to Active Record or DBI in 7 steps.

  1. Gather the installation sources you will need. You have to be registered with oracle to get their instant client packages. Download the ruby oci8 drivers

    Download the oracle instant client

    You want the following packages (these examples assume the zip format):

    • Instant Client Package - Basic or Instant Client Package - Basic Lite
    • Instant Client Package - SDK
    • Instant Client Package - SQL*Plus (optional but nice to have)

  2. Install oracle instant client packages

    I unzip them in the /opt directory and assume that for the examples. It doesn’t matter where you put them as long as you know where they are.

    The zip packages have one problem that needs to be solved after they are expanded. You must create a symlink in the install directory for a shared library: ln -s libclntsh.so.10.1 libclntsh.so

  3. Set up the oracle environment:

    I created a script called oracleenv.sh with the following lines in it:

         export ORACLE_HOME=/opt/instantclient_10_2/
     export LD_LIBRARY_PATH=/opt/instantclient_10_2/
     export PATH=/opt/instantclient_10_2/:$PATH
     export SQLPATH=/opt/instantclient_10_2/
     export TNS_ADMIN=/etc/

    and then I source it when I want to use something oracle related ‘. oracleenv.sh’. You may want to just put it in your profile.

    You also need to create a /etc/tnsnames.ora file. This can be tricky. The best option is probably to ask your DBA to create it for you. Here is an example:

     DEVDB.WORLD =
   (DESCRIPTION =
     (ADDRESS_LIST =
         (ADDRESS =
           (COMMUNITY = tcp.world )
           (PROTOCOL = TCP)
           (Host = 192.168.1.125)
           (Port = 1546)
         )
     )
     (CONNECT_DATA = (SID = DEVDB)
     )
   )

    The main thing you need for ruby later is the SID. At this point you may want to try to use sqlplus to see if you can connect to the database.

  4. Build the ruby oci8 package 
       # untar
   ruby setup.rb config -- --with-instant-client=/opt/instantclient_10_2/
   make
   make install
  5. Give the low level API a test 
     ruby -r oci8 -e "OCI8.new('username', 'password', 'DEVDB.WORLD').exec('select sysdate from dual') do |r| puts r.join(','); end"

    Here you provide the username, password and tns name. If this returns the current date and time then the driver is installed correctly.

  6. Install DBI

    download the dbi tar file

    The current version is: dbi-0.1.0.tar.gz

    untar the archive

    configure it (for this example only dbi itself is included): ruby setup.rb config –with=dbi

    build: ruby setup.rb setup

    install: ruby setup.rb install

    test:

     require 'dbi'
 dbh = DBI.connect('DBI:OCI8:DEVDB.WORLD', 'username', 'password')
 dbh.select_all('select sysdate from dual') do | row |
   p row
 end
 dbh.disconnect

    If this returns the current date and time then DBI is installed correctly.

  7. Install ActiveRecord

    I use gem to install active record: gem install activerecord

    After installing you should be able to use active record. Here is an example use:

     require 'rubygems'
 require 'active_record'

 ActiveRecord::Base.establish_connection(
     :adapter => "oci",
     :username => "username",
     :password => "password",
     :host => "DEVDB.WORLD")

 class TestTable < ActiveRecord::Base
   set_table_name "test_table"
   set_primary_key "some_id"
 end
 TestTable.find(:all).each do |tt|
   p tt
 end
 nac = TestTable.new()
 nac.id = 1001
 nac.some_column = "test"
 nac.save()

One thing I learned that is someone confusing is that active record turns your primary key into a variable named ‘id’. So if you don’t use auto generated primary keys for some reason you can’t just assign the primary key based on the name of the column as you can any other column in the table. Instead you need to use the id field of the model.

For more information check out the following links: