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.
-
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)
- 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
- Set up the oracle environment:
I created a script called oracleenv.sh with the following lines in it:
[code lang=”ini”]
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/
[/code]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:
[code lang=”ini”]
DEVDB.WORLD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(COMMUNITY = tcp.world )
(PROTOCOL = TCP)
(Host = 192.168.1.125)
(Port = 1546)
)
)
(CONNECT_DATA = (SID = DEVDB)
)
)
[/code]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.
- Build the ruby oci8 package
untar
[code lang=”ini”]ruby setup.rb config — –with-instant-client=/opt/instantclient_10_2/[/code]
make
make install - Give the low level API a test
[code lang=”ini”]ruby -r oci8 -e “OCI8.new(‘username’, ‘password’, ‘DEVDB.WORLD’).exec(‘select sysdate from dual’) do |r| puts r.join(‘,’); end”[/code]Here you provide the username, password and tns name. If this returns the current date and time then the driver is installed correctly.
- 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:[code lang=”ruby”]
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
[/code]If this returns the current date and time then DBI is installed correctly.
- 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:
[code lang=”ruby”]
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”
endTestTable.find(:all).each do |tt|
p tt
endnac = TestTable.new()
nac.id = 1001
nac.some_column = “test”
nac.save()
[/code]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:
- Info on oracle’s instant client
- Info on using dbi
- Info on dbi
- Info on rails with oracle
[tags]ruby, oracle, dbi, activerecord, rails[/tags]