DBA > Articles

Using JRuby with Oracle Database

By: Pas Apicella and Chris Jones
To read more DBA articles, visit http://dba.fyicenter.com/article/

This article explains how to use the JRuby programming language with Oracle Database 11g Release 2. JRuby allows the Java platform and extensive libraries to be augmented with an easy to use and powerful scripting language.

Software Requirements
The following software can be used:
Oracle Database 11g Release 2 Oracle Database Software Downloads
JDK 1.6 or later Java SE Downloads
JRuby 1.5.6 jruby.org
Oracle Database 11g Release 2 JDBC Driver
Download ojdbc6.jar from Oracle Database 11g Release 2 JDBC Drivers. Drivers for other versions of Oracle or the JDK can be downloaded from the JDBC Driver Downloads page. Sample Code
The example code can be downloaded here.

The examples will also work with some other versions.

Install each component using its recommended install instructions. Demonstration Setup

In a terminal window set the Oracle environment and verify Oracle's standard demonstration Human Resources (HR) schema is installed:

$ sqlplus hr/welcome
[. . .]
SQL> select table_name from user_tables;

If the schema is not available, see Installing the HR Schema in the Oracle Database Sample Schemas 11g Release 2 (11.2) guide.
Verify JDK 1.6 is installed:
$ java -version
java version "1.6.0_23"
Java(TM) SE Runtime Environment (build 1.6.0_23-b05)
Java HotSpot(TM) 64-Bit Server VM (build 19.0-b09, mixed mode)

Set JRUBY_HOME to the directory where JRuby is installed and verify JRuby runs:
$ export JRUBY_HOME=$HOME/jruby-1.5.6
$ export PATH=$PATH:$JRUBY_HOME/bin
$ jruby -v

jruby 1.5.6 (ruby 1.8.7 patchlevel 249) (2010-12-03 9cf97c3) (Java HotSpot(TM) 64-Bit Server VM 1.6.0_23) [amd64-java]
Copy the Oracle JDBC driver ojdbc6.jar into the $JRUBY_HOME/lib directory:
$ ls -l $JRUBY_HOME/lib
total 20084
-rw-r--r-- 1 pas usergrp 8102598 Dec 3 08:58 jruby.jar
drwxr-xr-x 13 pas usergrp 4096 Dec 3 08:57 native
-rw-r--r-- 1 pas usergrp 2152051 Feb 7 17:34 ojdbc6.jar
drwxr-xr-x 6 pas usergrp 4096 Dec 3 08:58 ruby

If JDK 1.5 is installed then copy ojdbc5.jar instead.
Running the Demonstration

The sample code shows several different data operations using JRuby and Oracle Database.
The jruby_plsql.sql file creates a PL/SQL package for showing how to call PL/SQL from JRuby:
-- jruby_plsql.sql
create or replace package emp_pack as
function get_employee_name (p_employee_id in number) return varchar2;
procedure delete_department (p_department_id in number);
end emp_pack;
show errors;

create or replace package body emp_pack as

function get_employee_name (p_employee_id in number) return varchar2 is
l_name varchar2(46) := null;
select first_name || ' ' || last_name
into (l_name)
from employees
where employee_id = p_employee_id;
return l_name;
end get_employee_name;

procedure delete_department (p_department_id in number) is
delete from departments where department_id = p_department_id;
end delete_department;

end emp_pack;
show errors;

The package can be installed using SQL*Plus:
$ sqlplus hr/welcome
SQL> @jruby_plsql.sql

Package created.
No errors.
Package body created.
No errors.
Verify installation by explicitly calling the package:
SQL> select emp_pack.get_employee_name(199) from dual;
Douglas Grant

The file jdbc_connection.rb contains code for connecting to the database using the Oracle JDBC driver.
It creates a new JRuby class OracleConnection that exposes attributes of DriverManager which is responsible for connecting to Oracle via the registered subprotocol OracleDriver.
# jdbc_connection.rb
require 'java'
java_import 'oracle.jdbc.OracleDriver'
java_import 'java.sql.DriverManager'

class OracleConnection
@conn = nil

def initialize (user, passwd, url)
@user = user
@passwd = passwd
@url = url

# Load driver class
oradriver = OracleDriver.new

DriverManager.registerDriver oradriver
@conn = DriverManager.get_connection url, user, passwd
@conn.auto_commit = false


# Add getters and setters for all attrributes we wish to expose attr_reader :user, :passwd, :url, :connection

def close_connection()
@conn.close() unless @conn

def prepare_call(call)
@conn.prepare_call call

def create_statement()

def prepare_statement(sql)
@conn.prepare_statement sql

def commit()

def self.create(user, passwd, url)
conn = new(user, passwd, url)

def to_s
"OracleConnection [user=#{@user}, url=#{@url}]"
alias_method :to_string, :to_s

Edit test_oracle.rb to define the HR schema username, password and connection string. This example shows the database is running on the local machine with the service name of orcl.

The file shows four common uses of SQL and PL/SQL.

# test_oracle.rb
require 'jdbc_connection'
# Edit these for your database schema
user = "hr"
passwd = "welcome"
url = "jdbc:oracle:thin:@localhost/orcl"

# Helpers to call our PL/SQL package
:1 := emp_pack.get_employee_name(:2);


print "Run at #{Time.now} using JRuby #{RUBY_VERSION}\n\n"
insert_stmt, select_stmt, rest, delete_stmt, function_stmt = nil
conn = OracleConnection.create(user, passwd, url)
# Display connection using the to_s method of OracleConnection
puts conn, "\n"

# Insert a new Department
puts "1. Inserting department 281 into the DEPARTMENTS table"
insert_sql = "insert into departments values (:1, :2, NULL, NULL)"
insert_stmt = conn.prepare_statement(insert_sql)
insert_stmt.set_int 1, 281
insert_stmt.set_string 2, "JRuby Department"

# Select all departments
puts "2. Showing all DEPARTMENTS rows"
select_sql = "select department_id, department_name from departments order by 1"
select_stmt = conn.create_statement
rset = select_stmt.execute_query select_sql
while (rset.next)
puts " Department [#{rset.getInt(1)}, #{rset.getString(2)}]"

# Call a PL/SQL package function to display an employee name
puts "3. Finding name of employee 200"
function_stmt = conn.prepare_call PLSQL_BLOCK_FUNCTION_CALL
function_stmt.set_int 2, 200
function_stmt.register_out_parameter 1, 12
puts " Employee 200 is #{function_stmt.get_string 1}\n"

# Call a PL/SQL package procedure to delete a department record
puts "4. Deleting department 281 from the DEPARTMENTS table"
delete_stmt = conn.prepare_call PLSQL_BLOCK_PROCEDURE_CALL
delete_stmt.set_int 1, 281

puts "\n** Error occured **\n"
puts "Failed executing Oracle demo from JRuby ", $!, "\n"

# We have to ensure everything is closed here.
if (!insert_stmt.nil?)
if (!select_stmt.nil?)
if (!rset.nil?)
if (!delete_stmt.nil?)
delete_stmt.close end
if (!function_stmt.nil?)


print "\nEnded at #{Time.now}\n"

After connecting, the file uses the exposed OracleConnection attributes to show these database operations:
* Inserting a new department 281. The department identifier and name are bound, allowing efficient use of database resources. * Querying all department records.
* Calling a PL/SQL function to find an employee name. The name is returned as an "OUT" parameter of the anonymous PL/SQL block in PLSQL_BLOCK_FUNCTION_CALL.
* Deletion of the newly created department 281 by calling a PL/SQL procedure.

Run the test program with JRuby:
$ jruby test_oracle.rb
Run at Fri Feb 11 14:54:25 -0800 2011 using JRuby 1.8.7

OracleConnection [user=hr, url=jdbc:oracle:thin:@localhost/orcl]
1. Inserting department 281 into the DEPARTMENTS table
2. Showing all DEPARTMENTS rows
Department [10, Administration]
Department [20, Marketing]
Department [30, Purchasing]
Department [40, Human Resources]
Department [50, Shipping]
Department [60, IT]
Department [70, Public Relations]
Department [80, Sales]
Department [90, Executive]
Department [100, Finance]
Department [110, Accounting]
Department [120, Treasury]
Department [130, Corporate Tax]
Department [140, Control And Credit]
Department [150, Shareholder Services]
Department [160, Benefits]
Department [170, Manufacturing]
Department [180, Construction]
Department [190, Contracting]
Department [200, Operations]
Department [210, IT Support]
Department [220, NOC]
Department [230, IT Helpdesk]
Department [240, Government Sales]
Department [250, Retail Sales]
Department [260, Recruiting]
Department [270, Payroll]
Department [281, JRuby Department]
3. Finding name of employee 200

Full article...

Other Related Articles

... to read more DBA articles, visit http://dba.fyicenter.com/article/