How to change your Rails database adapter from OCI to Oracle

I've started to use Rails migrations recently connecting to an Oracle database.

After reading many examples of how to configure the database.yml file on the Rails wiki (as well as ones from Oracle's own website), I ended up with something like:

development:
adapter: oci
host: database.com/mySID
username: schema_name
password: schema_name
This worked fine, but after researching other migration-related topics I read that oci was the 'old' way to connect to Oracle, the 'new' adapter to use is oracle.

A tutorial from Oracle showing usage of the new 'oracle' adapter (scroll down to the section entitled "Part 1: Of configuration, Cursors, and Rows") suggested that your config should look like this:
development:
adapter: oracle
host: DEBIAN
username: rails
password: rails
Note: DEBIAN was the name of their TNS entry specified in their local tnsadmin.ora file.

I didn't want to use a reference to a local tnsadmin.ora file on my machine, I wanted to specify the host and SID explicitly. After many failed attempts at specifying this in different formats I happened across this Rails patch ticket regarding the change from 'oci' to 'oracle' - the winning clue was:

To use the adapter with it's new name, simply change your database.yml file to include:
adapter: oracle
database: <your db name here>

Note that database is now used instead of host.

So the problem was that I was entering the database server details in the host field (like all the examples said to) but for a non-TNS named server you need to use the database field.

My fully working Oracle database.yml config entry now looks like this:
development:
adapter: oracle
database: database.com/mySID
username: schema_name
password: schema_name
Technorati Tags: , , , , ,

17 comments:

travis said...

Thank you!!!

abeacock said...

No problem Travis, and if you ever have any issues with Rails and Oracle, please post a comment on this thread and I'll try my best to help you out!

Niaz said...

Hi,
I am using the old way (ocl), but failing to connect to my development database. While I can connect to it from command prompt via sqlplus, RoR gives error.
My entry is:

development:
adapter: oci
host: ORCL
username: sac_cfg
password: myPass

If it helps, I have a database named ORCL installed in my local machine (Oracle 10g Enterprise Edition).
I also tried with my ip address but didn't work:
host: 10.16.35.164/ORCL

Your help would be greatly appreciated.

abeacock said...

Niaz, you HAVE to use Oracle as your adapter if you want to use the 'hostname/sid' connection method.

Apart from the above what do you type from sqlplus to get access? If you post that info I might be able to help further...

Holgi said...

Hey,
when using JRuby with Oracle, I had to use:

development:
adapter: oracle
host: localhost
database: XE
username: user
password: pass

HTH (someone :)

abeacock said...

Holgi, thanks for the tip!

dan said...

Having a problem connecting to Oracle 10.2.0.3. I can connect using the sample test your connection code. But the ruby scaffold command generates the following error.
C:\comics_catalog>ruby script/generate scaffold Comic
c:/ruby/lib/ruby/gems/1.8/gems/activerecord-2.0.2/lib/active_record/connection_a
dapters/abstract/connection_specification.rb:231:in `establish_connection': Plea
se install the oracle adapter: `gem install activerecord-oracle-adapter` (no suc
h file to load -- active_record/connection_adapters/oracle_adapter) (RuntimeErro
r)
Here is my database.yml
development:
adapter: oracle
database: LOCAL
username: ruby
password: ruby

Any clues? TIA, Dan

abeacock said...

Dan,

Have you got the activerecord-oracle-adapter gem installed?

The error message from script/generate suggests that you need to install a missing gem:

gem install activerecord-oracle-adapter

This is not a gem I've installed before as I added Oracle support by installing DBI/Ruby-OCI8.

Dan said...

I'm new to Ruby and following instructions from http://www.oracle.com/technology/pub/articles/haefel-oracle-ruby.html

When I try to install rails specifying V 1.0.0 I get an error.
c:\ruby>gem install rails -v 1.0.0 --remote
Bulk updating Gem source index for: http://gems.rubyforge.org
Install required dependency activesupport? [Yn] y
Install required dependency activerecord? [Yn] y
ERROR: While executing gem ... (Zlib::BufError)
buffer error

When I install the current version of 2.0 version of rails it can't connect to Oracle.

gem install activerecord-oracle-adapter displays GemNotFound error.

I have installed the OCI8 adapter
ruby ruby-oci8-1.0.0-mswin32.rb

Is there another distribution that I should try. I'm using the one click rubyinstaller, ruby186-26.exe Thank you.

abeacock said...

Dan,

Here's some more information on the gem missing issue, not sure if any of this helps, it's not a gem I've installed - http://www.ruby-forum.com/topic/135231

I'm doing my Rails development on an Ubuntu Linux system so there are going to be quite a number of differences but here are some suggestions:

* Have you installed DBI? On Linux it's called 'libdbi-ruby'

* Have you correctly installed OCI8? This is what I did on Linux:
tar zxvf ruby-oci8-1.0.0-rc2.tar.gz
cd ruby-oci8-1.0.0-rc2
ruby setup.rb config -- --with-instant-client
make
sudo make install

* Do you have the Oracle InstantClient drivers/libraries/utilities installed on Windows?
These are the Linux packages and versions that may or may not help!
oracle-instantclient-basic_10.1.0.4-2_i386.deb
oracle-instantclient-devel_10.1.0.4-2_i386.deb

Dan said...

Thanks for your help. I have Oracle Enterprise RDBMS 10.2.0.3 installed on Windows XP. This is what I had to execute to get activerecord-oracle-adapter installed.

gem install activerecord-oracle-adapter --source http://gems.r
ubyonrails.org
Bulk updating Gem source index for: http://gems.rubyonrails.org
Successfully installed activerecord-oracle-adapter-1.0.0

BTW - my version of rails is 2.02 and gem is 0.9.4

abeacock said...

Dan,

Now that you have the gem installed does it work or are you still having problems? If you are then please post a detailed log and I'll see if I can be of any use.

dan said...

The environment now works correctly. Thank you for your help.

abeacock said...

Glad you got it working, always happy to help!

Battur said...

Hello, guys.

I'm using rails 2.0.2, with Oracle RDBMS 11g on Windows XP.

I've installed:

-ruby-oci8 (used ruby-oci8-1.0.0-mswin32.rb)
-activerecord-oracle-adapter (downloaded as written in comments)

Rails, start fine. But when I see the application environment, web page says:

=======================
OCIError in Rails/infoController#properties

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

RAILS_ROOT: C:/eclipse/workspace/test
Application Trace | Framework Trace | Full Trace

env.c:257:in oci8lib.so
======================

I've installed oracle client 11g, and SQL developer works fine.
But my rails app doesn't. I couldn't find the solution, anybody helps?

Battur said...

Hello, guys.

It did work. I copied oci.dll, oraociei11.dll to Windows\System folder.

In the previous comment, I've made a mistake. The oracle db was on a remote machine.

Thanks.

Bill Comer said...

A bit late in the day as Andy's blog is dated June 2007 but I have just been having the same problem as Dan.

My solution is on my Blog