Ruby on Rails
Oracle (Version #249)

Rails supports Oracle 8i, 9i, and 10g.

Be sure to check out HowtoConnectToOracle

The “database: ” keyword can take two forms, depending on whether you have a system-wide TNSNAMES.ORA file.
If this is the case, you can simply refer to the database by SID, like so:

development:
  adapter: oracle
  database: examplesid
  username: exampleuser
  password: examplepass

Otherwise, you can specify an Easy Connect connection string. This form can be used if you are using the Oracle 10gR2 (or more recent) full client or Instant Client libraries to connect to the Oracle Database:

development:
  adapter: oracle
  database: 192.168.0.50/examplesid
  username: exampleuser
  password: examplepass

For older versions (before 2006 March?), use “adapter: oci” and “host: 192.168.0.50/examplesid” instead of “adapter: oracle” and “database: 192.168.0.50/examplesid” respectively.

Usage notes:

  • Any table or sequence you wish to access should be visible (as a synonym or otherwise) in the default schema for the user you specified in your database.yml file.
  • Key generation assumes a ”${table_name}_seq” sequence is available for all tables. o Note: Rails versions 0.13.1 and earlier used a single sequence shared across all tables, hard-coded to ‘rails_sequence’.
  • The sequence name can be changed using ActiveRecord::Base.set_sequence_name
  • Oracle uses DATE or TIMESTAMP datatypes for both dates and times. Consequently I have had to resort to some hacks to get data converted to Date or Time in Ruby. If the column_name ends in _time it’s created as a Ruby Time. Else if the hours/minutes/seconds are 0, I make it a Ruby Date. Else it’s a Ruby Time. This is imperfect – but if you use Duck Typing you’ll probably not care very much (fixed in 6388 ?).
  • Timezones and sub-second precision on timestamps are not supported.
  • Default values that are functions (such as “SYSDATE”) are not supported. This is a restriction of the way ActiveRecord supports default values.
  • Referential integrity constraints are not fully supported. Under at least some circumstances, ActiveRecord appears to delete parent and child records out of order and out of transaction scope. (Or this may just be a problem of test setup.)

Determining the SID

The SID can be found in the first field of your oratab file (often /etc/oratab on Linux). For example, the default SID for Oracle Express Edition is “XE”.

/etc/oratab

XE:/usr/lib/oracle/xe/app/oracle/product/10.2.0/server:N

database.yml

development:
  adapter: oracle
  database: 127.0.0.1/XE
  username: example
  password: examplepass

Getting the driver:

The OCI8 driver can be retrieved from http://rubyforge.org/projects/ruby-oci8/

MacOS X users may need some additional configuration.
Instructions for intel can be found here:
http://www.foliosus.com/2007/11/19/connecting-ruby-on-rails-to-oracle-on-an-intel-mac-in-leopard-mac-osx-105/

Where can I find help?

The best place to start is the OTN Discussion Forums at http://forums.oracle.com. There are users and developers there 24 hours a day answering questions. Be sure to use the search feature to see if your problem has already been addressed before posting!

You may also want to review the Oracle+RoR FAQ published on OTN at http://otn.oracle.com/pub/articles/saternos-ror-faq.html.

If your problem turns out to Rails-specific, try the RubyOnRails mailing list.
Resources

  • Oracle Instant Client
  • Oracle FAQ: http://www.orafaq.com/
  • Common Connectivity Issues -Troubleshooting : http://dbalifeline.com/oracle/oracle_connectivity_frequently_asked_questions_faqs
    Troubleshooting
    (1) Test your Ruby/Oracle connectivity
    ruby -r oci8 -e 'OCI8.new("scott", "tiger", "examplesid").exec("select * from emp") do |r| puts r.join(","); end'

    The above should return results in a default oracle install where “examplesid” is the sid from your local tnsnames.ora or, //hostname_or_ip/sid_on_server
    (2) Test that active_record works
    require 'rubygems'
require 'active_record'

class Emp < ActiveRecord::Base
  set_table_name "EMP" 
end

ActiveRecord::Base.establish_connection(
  :adapter  => "oracle",
  :database => "examplesid",
  :username => "scott",
  :password => "tiger" 
)

emp = Emp.find(:first,:conditions =>["ENAME = ?", "SMITH"])
puts "#{ emp.empno}, #{ emp.ename}, #{emp.job}, #{emp.hiredate}"

The above should print a single Emp with a default Oracle install with SID = examplesid.
(3) Test that database.yml is parsed correctly

ruby -ryaml -e "File.open('config/database.yml') { |f| puts YAML.load(f).inspect }"

The above should print out the name value pairs. Beware of tabs (\t), as YAML forbids tabs. See the below output for an example that will cause a: ORA-12162: TNS:net service name is incorrectly specified

{”\tpassword”=>“tiger”, “development”=>{“adapter”=>“oracle”}, ”\tusername”=>“scott”, ”\tdatabase”=>“examplesid”}

Note the \t characters.
See Also

  • HowtoConnectToOracle o I actually find that HowtoConnectToOracle is more correct – using the settings on this page didn’t work for me at all (Oracle 9i): I had to specify both “database” and “host” as show on the other page, when using TNSNAMES.

Unicode

If you are having trouble writing and reading utf8, check this out .It might save you.

Problems

I have tried all the connection strings on this page and I still get a TNS names error. I connect without problem using Perl or Python so I know my Oracle is setup correctly. Hints?

Category:DatabaseDrivers

Question
On p. 362 (or 359), Agile Web Development With Rails, 2nd Edition has a footnote that says Oracle 8 users cannot use preloading, presumably because left outer joins are required (9+ can). Now, I’ve used Oracle 8i, and it definitely has left outer joins, just with a different syntax. Does the current (1.2) adapter support preloading on 8i?—John Duncan

Answer (?) Rails 0.10.0 (24th February, 2005) added a OCI8-based Oracle adapter that has been verified to work with Oracle 8i through 10g

Rails versions after 0.13.1 have much-enhanced Oracle support, including separate sequences for each table, synonyms, etc.
See the relevant patch for more information:
http://dev.rubyonrails.org/ticket/1798
Rails configuration:

Using ‘oracle’ rather than ‘oci’ as an adapter

(Full article here)

If you want to use the ‘new’ oracle adapter rather than oci and you want to use host/SID style of database server reference you have to use ‘database’ rather than ‘host’, see [PATCH] Rename oracle_adapter, keep up w/ the Joneses for more details. – AndrewBeacock

Question
Does the database have to be on localhost?

Example Connection
ActiveRecord::Base.establish_connection( :adapter => “oracle”, :database => “hostname:port/sid”, :username => “someone”, :password => “somepassword”
)

The atabase will look like one of these examples :database => “127.0.0.1:1521/XE” :database => “dbserver:1521/sales”

Rails supports Oracle 8i, 9i, and 10g.

Be sure to check out HowtoConnectToOracle

The “database: ” keyword can take two forms, depending on whether you have a system-wide TNSNAMES.ORA file.
If this is the case, you can simply refer to the database by SID, like so:

development:
  adapter: oracle
  database: examplesid
  username: exampleuser
  password: examplepass

Otherwise, you can specify an Easy Connect connection string. This form can be used if you are using the Oracle 10gR2 (or more recent) full client or Instant Client libraries to connect to the Oracle Database:

development:
  adapter: oracle
  database: 192.168.0.50/examplesid
  username: exampleuser
  password: examplepass

For older versions (before 2006 March?), use “adapter: oci” and “host: 192.168.0.50/examplesid” instead of “adapter: oracle” and “database: 192.168.0.50/examplesid” respectively.

Usage notes:

  • Any table or sequence you wish to access should be visible (as a synonym or otherwise) in the default schema for the user you specified in your database.yml file.
  • Key generation assumes a ”${table_name}_seq” sequence is available for all tables. o Note: Rails versions 0.13.1 and earlier used a single sequence shared across all tables, hard-coded to ‘rails_sequence’.
  • The sequence name can be changed using ActiveRecord::Base.set_sequence_name
  • Oracle uses DATE or TIMESTAMP datatypes for both dates and times. Consequently I have had to resort to some hacks to get data converted to Date or Time in Ruby. If the column_name ends in _time it’s created as a Ruby Time. Else if the hours/minutes/seconds are 0, I make it a Ruby Date. Else it’s a Ruby Time. This is imperfect – but if you use Duck Typing you’ll probably not care very much (fixed in 6388 ?).
  • Timezones and sub-second precision on timestamps are not supported.
  • Default values that are functions (such as “SYSDATE”) are not supported. This is a restriction of the way ActiveRecord supports default values.
  • Referential integrity constraints are not fully supported. Under at least some circumstances, ActiveRecord appears to delete parent and child records out of order and out of transaction scope. (Or this may just be a problem of test setup.)

Determining the SID

The SID can be found in the first field of your oratab file (often /etc/oratab on Linux). For example, the default SID for Oracle Express Edition is “XE”.

/etc/oratab

XE:/usr/lib/oracle/xe/app/oracle/product/10.2.0/server:N

database.yml

development:
  adapter: oracle
  database: 127.0.0.1/XE
  username: example
  password: examplepass

Getting the driver:

The OCI8 driver can be retrieved from http://rubyforge.org/projects/ruby-oci8/

MacOS X users may need some additional configuration.
Instructions for intel can be found here:
http://www.foliosus.com/2007/11/19/connecting-ruby-on-rails-to-oracle-on-an-intel-mac-in-leopard-mac-osx-105/

Where can I find help?

The best place to start is the OTN Discussion Forums at http://forums.oracle.com. There are users and developers there 24 hours a day answering questions. Be sure to use the search feature to see if your problem has already been addressed before posting!

You may also want to review the Oracle+RoR FAQ published on OTN at http://otn.oracle.com/pub/articles/saternos-ror-faq.html.

If your problem turns out to Rails-specific, try the RubyOnRails mailing list.
Resources

  • Oracle Instant Client
  • Oracle FAQ: http://www.orafaq.com/
  • Common Connectivity Issues -Troubleshooting : http://dbalifeline.com/oracle/oracle_connectivity_frequently_asked_questions_faqs
    Troubleshooting
    (1) Test your Ruby/Oracle connectivity
    ruby -r oci8 -e 'OCI8.new("scott", "tiger", "examplesid").exec("select * from emp") do |r| puts r.join(","); end'

    The above should return results in a default oracle install where “examplesid” is the sid from your local tnsnames.ora or, //hostname_or_ip/sid_on_server
    (2) Test that active_record works
    require 'rubygems'
require 'active_record'

class Emp < ActiveRecord::Base
  set_table_name "EMP" 
end

ActiveRecord::Base.establish_connection(
  :adapter  => "oracle",
  :database => "examplesid",
  :username => "scott",
  :password => "tiger" 
)

emp = Emp.find(:first,:conditions =>["ENAME = ?", "SMITH"])
puts "#{ emp.empno}, #{ emp.ename}, #{emp.job}, #{emp.hiredate}"

The above should print a single Emp with a default Oracle install with SID = examplesid.
(3) Test that database.yml is parsed correctly

ruby -ryaml -e "File.open('config/database.yml') { |f| puts YAML.load(f).inspect }"

The above should print out the name value pairs. Beware of tabs (\t), as YAML forbids tabs. See the below output for an example that will cause a: ORA-12162: TNS:net service name is incorrectly specified

{”\tpassword”=>“tiger”, “development”=>{“adapter”=>“oracle”}, ”\tusername”=>“scott”, ”\tdatabase”=>“examplesid”}

Note the \t characters.
See Also

  • HowtoConnectToOracle o I actually find that HowtoConnectToOracle is more correct – using the settings on this page didn’t work for me at all (Oracle 9i): I had to specify both “database” and “host” as show on the other page, when using TNSNAMES.

Unicode

If you are having trouble writing and reading utf8, check this out .It might save you.

Problems

I have tried all the connection strings on this page and I still get a TNS names error. I connect without problem using Perl or Python so I know my Oracle is setup correctly. Hints?

Category:DatabaseDrivers

Question
On p. 362 (or 359), Agile Web Development With Rails, 2nd Edition has a footnote that says Oracle 8 users cannot use preloading, presumably because left outer joins are required (9+ can). Now, I’ve used Oracle 8i, and it definitely has left outer joins, just with a different syntax. Does the current (1.2) adapter support preloading on 8i?—John Duncan

Answer (?) Rails 0.10.0 (24th February, 2005) added a OCI8-based Oracle adapter that has been verified to work with Oracle 8i through 10g

Rails versions after 0.13.1 have much-enhanced Oracle support, including separate sequences for each table, synonyms, etc.
See the relevant patch for more information:
http://dev.rubyonrails.org/ticket/1798
Rails configuration:

Using ‘oracle’ rather than ‘oci’ as an adapter

(Full article here)

If you want to use the ‘new’ oracle adapter rather than oci and you want to use host/SID style of database server reference you have to use ‘database’ rather than ‘host’, see [PATCH] Rename oracle_adapter, keep up w/ the Joneses for more details. – AndrewBeacock

Question
Does the database have to be on localhost?

Example Connection
ActiveRecord::Base.establish_connection( :adapter => “oracle”, :database => “hostname:port/sid”, :username => “someone”, :password => “somepassword”
)

The atabase will look like one of these examples :database => “127.0.0.1:1521/XE” :database => “dbserver:1521/sales”

Created on April 07, 2008 17:42 by Michael Teter (68.113.203.78)
Rollback | Forward in time (1 more) | Back in time (248 more)