Developer: Open Source

   DOWNLOAD
 Oracle Database XE
 Oracle Instant Client
 Ruby on Rails
   TAGS
ruby, rubyonrails, All

Ruby on Rails with Oracle FAQ


by Casimir Saternos

Exploring Ruby on Rails (RoR)? This FAQ provides basic information about connecting to Oracle, installation, and creating Oracle Database XE + RoR applications.

Published December 2006

Contents

  1. WHY RUBY ON RAILS?
  2. DEFINITIONS
  3. ORACLE DATABASE CONNECTIONS
  4. INSTALLATION
  5. CREATING AN APPLICATION

1. WHY RUBY ON RAILS?

Many platforms are available for Web development including Microsoft’s .NET, Java 2 Enterprise Edition, and PHP. Why, then, is there so much interest in a new platform using a relatively unfamiliar language? 

By its design, Ruby on Rails (RoR) lets you develop Web applications very quickly. It does this by adhering to conventions rather than forcing you to make all of your configuration decisions upfront. As a result, RoR lets you develop running applications in a surprisingly short length of time. Once you install Ruby and its various required packages, you can create a minimal Web application by running less than a half-dozen commands and editing one file (database.yml). RoR includes code generators or libraries that address many of the most common Web development tasks. The Ruby language has the object-oriented capabilities of a language like Java, yet you can also use it to create simple scripts. The language’s range of capabilities increases your ability to create applications without having to resort to numerous divergent technologies.


2. DEFINITIONS


What is Ruby?

Ruby is an object-oriented interpreted scripting language created by Yukihiro Matsumoto. Since the mid 1990s, it has grown in popularity in Japan and has gained recent attention as the language behind the Rails framework. You can use the language for a striking variety of functions—from basic scripting to creating Windows applications to developing Web applications. You can start using Rails without extensive knowledge of the language; the Rails framework generates the fundamental classes needed for the Web application and uses reflection to perform object-relational mapping to the database.

What is Rails?

Rails is a Web application and persistence framework created by David Heinemeier Hansson. It includes the infrastructure you need to create database-backed Web applications that use the Model-View-Control (MVC) pattern. Rails has gained attention as a development framework that lets you rapidly create full-featured database-backed Web applications.

What is the Model-View-Controller Pattern (MVC)?

The MVC design pattern is used to separate the following concerns of an application:

Model:
Application data, including validation rules
View:
The way data is presented to the end user
Controller:
The way a user manipulates the data within the model

This pattern has become popular for Web application development, but it has existed for much longer. Implementations of the pattern have been refined over time and products have been developed to implement it as a framework. You can use Java projects such as Struts and Hibernate to develop applications that leverage the MVC architecture. 

In the Rails framework, ActiveRecord deals with Model concerns. It maps database tables to Ruby objects and provides many other features, including a way to easily access data, represent and traverse relationships between tables, validate data, and get data summaries. It also lets you use straight SQL when desired.

The ActionController coordinates interactions between the View and Model layers (letting users change data through the Web). It routes all requests sent to the Web application, provides a filtering mechanism, Web session access, and other features.

The ActionView deals with what’s actually displayed to the user (generally in the Web browser). Files that contain HTML and Ruby code are appended with an .rhtml extension. Helper classes are also available to provide data formatting.

What is RubyGems?

RubyGems (or simply gems) is the Ruby packaging system used to package Rails components. It provides a standard format for distributing Ruby programs and libraries as well as a tool for managing package installation.

What is OCI8?

The Ruby/Oracle Call Interface (OCI8) is a database driver for Ruby/DBI. RubyDBI provides a database-independent interface for Ruby. You install OCI8 to provide a Ruby interface to the underlying Oracle client software. It’s somewhat analogous to ODBC or JDBC.


3. ORACLE DATABASE CONNECTIONS


What do I need to install to run Ruby on Rails using Oracle Database?

The minimum installation for an RoR Web application that integrates with Oracle Database must include the following:

  • Oracle SQL*Plus or Oracle Instant Client software
  • Ruby
  • Gem
  • OCI8
  • Rails

The Rails installation includes an application server sufficient for development and small implementations (WEBrick - for WEB server toolkit).

You can use your favorite text editor to edit source code files associated with your Web application.

You’ll need to install either an Oracle Client or Instant Client to work with the OCI8 software. You must configure the TNSNAMES.ora file with the connection information that lets the Oracle client software connect with the database. A great way to get started is to download the free Oracle Database XE, which will install the Oracle client and database.

Strictly speaking, you don’t need SQL*Plus to run Rails applications. There are Rails-based methods (such as migrations) of creating and maintaining database schemas. 

How can I test OCI8 connectivity?

To do a simple test to see if you can query a database that contains the demo (HR) schema, run the following one-liner. The output will be a pipe-delimited file. Make sure the HR schema exists and the account is unlocked and enabled. Substitute the name of your Oracle database (designated below as ORCL) and HR password (indicated as hr_password below) in the following command:

ruby -r oci8 -e "OCI8.new('hr', 'hr_password', 'ORCL').exec('SELECT * FROM jobs
ORDER BY 1') do |r| puts  r.join(' | '); end"

If the connection and query were successful, the output should look something like this:

AC_ACCOUNT | Public Accountant  | 4200 | 9000
AC_MGR | Accounting Manager  | 8200 | 16000
AD_ASST | Administration  Assistant | 3000 | 6000
AD_PRES | President | 20000  | 40000
AD_VP | Administration Vice  President | 15000 | 30000
FI_ACCOUNT | Accountant |  4200 | 9000
FI_MGR | Finance Manager |  8200 | 16000
HR_REP | Human Resources  Representative | 4000 | 9000
IT_PROG | Programmer | 4000  | 10000
MK_MAN | Marketing Manager  | 9000 | 15000
MK_REP | Marketing  Representative | 4000 | 9000
PR_REP | Public Relations  Representative | 4500 | 10500
PU_CLERK | Purchasing Clerk  | 2500 | 5500
PU_MAN | Purchasing Manager  | 8000 | 15000
SA_MAN | Sales Manager |  10000 | 20000
SA_REP | Sales  Representative | 6000 | 12000
SH_CLERK | Shipping Clerk |  2500 | 5500
ST_CLERK | Stock Clerk |  2000 | 5000
ST_MAN | Stock Manager |  5500 | 8500

How do I configure an Oracle TNSNAMES.ora entry?

The TNSNAMES.ora file is a plain-text file used by Oracle to determine the information needed to connect to a database. You’ll typically find it within the Oracle Home /network/admin directory. Every connection includes the server name (or IP address), the port where the Oracle Listener is running, and the name of the database in view.

Here’s an example of a TNS entry that might appear in the file:

ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = server.company.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = ORCL)
    )
  )

If you’re using Oracle Express Edition, an entry named XE will appear in this file, indicating the database that was created when you installed the software. 

To create a new TNS Entry, you can copy an existing entry and modify the database name, port number, and server name accordingly. Depending on your installation, you might also be able to perform the same task with the Oracle Net Configuration Assistant, which provides a GUI that will edit the file for you.

What is Oracle Instance Client?

Oracle
Instant Client is software that lets you run your applications without installing the standard Oracle client or having an ORACLE_HOME. You can find a description of the software, download the file, and get installation instructions here:
http://www.oracle.com/technology/tech/oci/instantclient/index.html

Once you install Oracle Instant Client, you can reference databases using Oracle Easy Connect Naming.

What is Oracle Easy Connect Naming?

If you don’t have a database entry specified in your Oracle TNSNAMES.ora, you can make a connection using easy connect naming.  This lets you connect to an Oracle database using only a TCP/IP connect string made up of a host name, port, and service name. You can run a test of OCI8 using Easy Connect Naming as follows (substituting the appropriate values for your password, server, port, and database):

ruby -r oci8 -e "OCI8.new('hr',  'hr_password', 
'//server.company.com:1521/orcl').exec('SELECT * FROM jobs order  by 1) do |r| 
puts r.join(' | '); end"

4. INSTALLATION


How do I install Rails and its dependencies?

Before you install Rails, you must install Ruby and Gems. 

To install Rails and its dependencies, run the following command:

gem install rails  --include-dependencies

A number of gems are required for Rails to function. These are the versions we used to create the application described in this FAQ:

Package Name

File Name (including version)

Description

actionmailer

actionmailer-1[1].1.5.gem

E-mail support

actionpack

actionpack-1[1].11.2.gem

Controller and View Framework

actionwebservice

actionwebservice-1[1].0.0.gem

Web Service Support

activerecord

activerecord-1[1].13.2.gem

Object Relational Mapping Layer

activesupport

activesupport-1[1].2.5.gem

Utility Classes Used by Rails

Rails

rails-1[1].0.0.gem

Ruby-based MVC Web Application Framework

Rake

rake-0[1].6.2.gem

Ruby Build Program

If you’re behind a firewall or if, for other reasons, you can’t access the remote gems, you can install the software locally.

How do I install Rails and its dependencies if a remote installation fails?

If your machine is located behind a firewall or if, for other reasons, you can’t access the remote gems, download Rails and its dependencies from http://www.rubyforge.org and transfer them to your computer. You can then install each gem locally using a command in the following form:

gem install <gem name up to the dash  preceding the version number>.

For instance:

gem install actionmailer

How do I install Ruby on Rails on the Windows OS?

1. Install Ruby

Installing Ruby on Windows is relatively straightforward: download the appropriate version of Ruby (we used ruby182-15.exe with this article) and double-click the installer. If you’re not sure which components to install, choose Full Install. In any case, you’ll need the RubyGems Package Manager to proceed with the Rails installation.

To make sure that both Ruby and Gems were installed correctly, run gem list --local at the command line to list local gems. The output should looks something like this:

*** LOCAL GEMS ***

fxri (0.3.2)
    Graphical interface to the RI documentation, with search engine.

fxruby (1.2.6)
    FXRuby is the Ruby binding to the FOX GUI toolkit.

sources (0.0.1)
    This package provides download sources for remote gem installation

The installation steps and links to the various resources are listed on the Ruby on Rails Web site ( http://www.rubyonrails.org/down).

2. Install Oracle Database Connectivity Support

Download the Ruby interface for Oracle from http://www.rubyforge.org/projects/ruby-oci8. This software provides an interface between Oracle Database 8, Oracle Database 9, or Oracle Database 10 through the standard Oracle Client software or through Instant Client. Make sure you choose the Windows version. Navigate to the directory that contains the Ruby program that you downloaded (ruby-oci8-0[1].1.13-mswin.rb at the time of this writing). Run the following command and choose Yes when prompted:

C:\>ruby ruby-oci8-0[1].1.13-mswin.rb

Copy OCI8.rb to c:/ruby/lib/ruby/site_ruby/1.8/DBD/OCI8
Copy oci8.rb to c:/ruby/lib/ruby/site_ruby/1.8
Copy oci8lib.so to c:/ruby/lib/ruby/site_ruby/1.8/i386-msvcrt
OK?
Enter Yes/No: Yes
Copying OCI8.rb to c:/ruby/lib/ruby/site_ruby/1.8/DBD/OCI8 ... done
Copying oci8.rb to c:/ruby/lib/ruby/site_ruby/1.8 ... done
Copying oci8lib.so to c:/ruby/lib/ruby/site_ruby/1.8/i386-msvcrt ... done
OK

3. Test the OCI8 connectivity

See How can I test OCI8 connectivity?

4. Install Rails and its dependencies

See How do I install Rails and its dependencies?

5. Verify the Rails Installation

Using the following command:

rails -–help

should display a Usage message followed by available options:

Usage: c:/ruby/bin/rails /path/to/your/app [options]
.
.
.

How do I install Ruby on Rails on Linux?

1. Install Ruby

Installing Ruby on Linux requires a few extra steps that may not be familiar to those new to Linux. After downloading the appropriate files, follow the same basic steps of installing Oracle client software, Ruby, Gems, OCI8, and Rails (and its dependencies) described in the Windows Installation section.  To install Ruby, extract the archive that contains it and build it:

tar -xzvf ruby-1.8.2.tar.gz 
cd ruby-1.8.2
./configure
make
make install

2. Install Gems

To install Gems, extract the archive that contains it and run the Ruby setup program:

tar -xzvf rubygems-0\[1\].8.11.tgz
cd rubygems-0.8.11
ruby setup.rb

3. Install OCI8

export ORACLE_HOME=<path>
tar -xzvf ruby-oci8-0\[1\].1.13.tar.gz 
cd ruby-oci8-0.1.13

To display the three steps that need to be run, invoke the setup program (ruby setup.rb) and pass it zero parameters.

# ruby setup.rb
No task or global option given.
Typical installation procedure is:
    $ ruby setup.rb config
    $ ruby setup.rb setup
    # ruby setup.rb install  (may require root privilege)
Try 'ruby setup.rb --help' for detailed usage.

You must run these three options in order. To verify that OCI8 and Rails were installed, follow the same steps as listed above for Windows.

4. Install Rails and it dependencies

See How do I install Rails and its dependencies?

5. Verify the Rails installation

The following command:

rails -–help

should display a Usage message followed by available options:

Usage: /usr/local/bin/rails /path/to/your/app [options].
.
.

Errors During Installation

How do I handle an error that indicates a RubyGem could not be found?

If you don’t successfully install dependent packages before you install the packages that require them, you’ll get an error message such as could not find RubyGem rake (>= 0.6.2)(Gem::LoadError).

The first line of the error lists the dependent package and the version required (rake 0.6.2 or above in the example). Install the dependent package and then reinstall.

How do I handle an error indicating that a gem could not be found?

During a remote installation you might see an error like this. It may occur if you don’t have Internet access or if you’re behind a firewall. If so, a number of possible messages might appear. For instance:

Local gem file not found: rails*.gem
Attempting remote installation of 'rails'
Updating Gem source index for: http://gems.rubyforge.org
ERROR:  While executing gem ... (OpenURI::HTTPError)
    403 Forbidden

You have a number of options, depending upon your particular network configuration:

  1. If you’re behind a proxy server, you may be able to run gem install with the –http-proxy option. For instance:
    gem install  rails –v 1.1 –http-proxy http://www-proxy.us.oracle.com:80
    

    However, if the proxy server requires authentication, this method may fail.

  2. Download the gems and install them using the –local option. See How do I install Rails and its dependencies if a remote installation fails?

How do I handle an error that says the service name cannot be resolved?

If you specify an ORACLE_SID that doesn’t exist in your tnsnames.ora, you’ll get an error message ORA-12154: TNS:could not resolve service name (OCIError).

The fact that an ORA- error was thrown shows that Ruby is communicating with the Oracle client. Remember that Ruby is using Oracle client software (or Instant Client) to make a connection. Make sure you have the correct connection information in your tnsnames.ora. If you have multiple Oracle Homes, make sure you configure the tnsnames.ora within the correct Oracle Home.

What are some Linux-specific errors that can occur?

When using Linux, you might encounter a variety of errors related to your environment. These will vary based upon your installation, versions of existing packages, which user you’re using, how permissions are set on your system, and how environmental variables (such as PATH) are set. 

For instance, if you weren’t running make install (while installing Ruby) with root permissions, the installation will fail if you don't have permissions to several directories in /usr/local. If an older version of Ruby is specified first in your PATH variable, the gem might display an error stating No such file to load -- rubygems (LoadError).


5. CREATING AN APPLICATION


What are the Database Object Conventions required by Rails when using Oracle?

Rails is designed to use a number of conventions related to database objects. You can work around them (and you’ll need to when working with legacy databases), but adhering to them greatly speeds application development and results in code that’s easier to maintain. Two conventions are fundamental to Rails development:

  1. Table names should be plural. The table will map to a Model object that has the same name but in the singular form.

  2. A primary key of type NUMBER named id should be created for each table. This column will serve as the unique identifier used by Rails to retrieve records by default. When using Oracle applications, create a sequence that uses the table name followed by “_seq”. 

A number of column names have special significance within a Rails application. The id column serves as the primary key, as mentioned above. Columns that contain ids for related tables are named in the format <singular_form_of_table>_id. A number of columns can be used to track the date and time at which records were changed (created_at, created_on, updated_at, updated_on). Other columns are available to count child records, perform Rails-controlled optimistic locking, and hierarchically categorize data (for use in lists and trees).

How do I create a basic Rails application?

Once you’ve installed the software, you can immediately begin developing your first Web application. 

1. Configure the Database

Connect to the database you intend to use for the application through SQL*Plus and create your database objects.  See What are the Database Object Conventions required by Rails when using Oracle?

2. Create the Rails Web application

Navigate to working directory and create a Rails application:

rails name_of_your_application

Subsequent commands will run scripts that are relative to the newly created application directory, so navigate to this directory using the following command:

cd name_of_your_application

3. Configure the application to use the Database

Edit the database.yml file found in the config directory. Delete the contents of the file and replace them with the Oracle-specific entries. Your file should look something like this:

development:
  adapter: oci
  host: ORCL
  username: rubydev
  password: rubydev

test:
  adapter: oci
  host: ORCL
  username: rubytest
  password: testpassword1

production:
  adapter: oci
  host: PROD
  username: rubyprod
  password: secure_password54

4. Create the basic Model, Views, and Controller

Run the following command, which will create the scaffold (in essence, an entire skeleton Web application) based upon the table in the user schema:

ruby script\generate scaffold model_name_here

Substitute the name of your model (the singular form of your table name) for model_name_here.

This command created the Model, Views, and Controller for the Web application. The Model provides an object mapping to our underlying database table. The Controller will provide navigation between the various views and communication across the model. The Views will provide the interface that will let a user create, update, delete, and list records in the products table.

5. Test the newly created application

First, start the (included) Web server:

ruby script\server

Now navigate to:

http://localhost:3000/plural_of_your_model_name_here

A basic listing of the records in the table will appear. For example, a model named product that maps to a table named products might display a listing like this:

Click the links to display, update, delete, or insert new records.

Casimir Saternos is an Oracle Certified DBA, IBM Certified Enterprise Developer, and Sun Certified Java Programmer based in Allentown, Penn.