Gnana Prasad Ruby

1) 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

2) 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

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.

3) 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.

4) How can I test OCI8 connectivity?

In order to connect to Oracle, you need to install the Ruby/Oracle Call Interface (OCI8) library, which is a database driver based on Ruby/DBI (Database Interface module).

RubyDBI provides a database-independent interface for Ruby to talk to databases similar to JDBC or ODBC. The Ruby OCI8 driver provides connectivity to Oracle versions 8 through 10 via standard Oracle Client software. OCI8 is a Ruby wrapper written around native C code which does the actual interaction.

ruby-oci8 is a ruby interface for Oracle Database. This is available with Oracle8, Oracle8i, Oracle9i, Oracle10g, Oracle XE and Oracle Instant Client

To download and install Oracle Rails library, go to http://rubyforge.org/projects/ruby-oci8,

Download ruby-oci8-0.1.13-mswin.rb to your C drive, and then using your command window, execute the following command.

                      
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

Note: To do a simple test to see if you are able to connect to the Oracle database server.

Run the following command on the command line with the specified fields like Username, 

Password, Net service name

 
ruby -r oci8 -e "OCI8.new('<user_name>', '<password>', 'NET_SERVICE_NAME').exec('SELECT * FROM jobs ORDER BY 1') do |r| 
puts r.join(' | '); end"

Note: <user_name>: User name of your Oracle database
      <password>: Password of your Oracle database
      <net_service_name>: Service name which given in TNSNAMES.ora file
 
TNSNAMES.ora:  Is a SQL*Net configuration file that defines databases addresses for establishing
connections to them. This file normally resides in the ORACLE HOME\NETWORK\ADMIN directory.

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

5) 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.

6) 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"

7) How do I install oci8 on Linux?

Install OCI8

export ORACLE_HOME=

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.

Errors During Installation

8) 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.

9) 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).

10) How do I create a basic Rails application?

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

a) 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?

b) 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

c) Copy Oracle_adapter.rb to ruby installed gems path

Copy the oracle_adapter.rb file into the ruby installed gems path as specified below:

C:\ruby\lib\ruby\gems\1.8\gems\activerecord-2.0.2\lib\active_record\connection_adapters

d) 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: ocihost: ORCL
# Name specified in a TNSNAMES.org file
username: *******
password: *******
test:
adapter: oci
host: ORCL
username: ******
password: ******
production:
adapter: oci
host: PROD
username: ********
password: ********

6. 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.

7. 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