Ruby on Rails with Oracle FAQ

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?

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:

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.

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?

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:

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?

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

How do I install Ruby on Rails on Linux?

Errors During Installation

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.