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;
TABLE_NAME
------------------------------
COUNTRIES
JOBS
EMPLOYEES
LOCATIONS
DEPARTMENTS
JOB_HISTORY
REGIONS
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;
begin
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
begin
delete from departments where department_id = p_department_id;
commit;
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;
EMP_PACK.GET_EMPLOYEE_NAME(199)
-----------------------------------
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
end
# Add getters and setters for all attrributes we wish to expose
attr_reader :user, :passwd, :url, :connection
def close_connection()
@conn.close() unless @conn
end
def prepare_call(call)
@conn.prepare_call call
end
def create_statement()
@conn.create_statement
end
def prepare_statement(sql)
@conn.prepare_statement sql
end
def commit()
@conn.commit
end
def self.create(user, passwd, url)
conn = new(user, passwd, url)
end
def to_s
"OracleConnection [user=#{@user}, url=#{@url}]"
end
alias_method :to_string, :to_s
end
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
PLSQL_BLOCK_FUNCTION_CALL = <<EOF
begin
:1 := emp_pack.get_employee_name(:2);
end;
EOF
PLSQL_BLOCK_PROCEDURE_CALL = <<EOF
begin
emp_pack.delete_department(:1);
end;
EOF
print "Run at #{Time.now} using JRuby #{RUBY_VERSION}\n\n"
insert_stmt, select_stmt, rest, delete_stmt, function_stmt = nil
begin
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"
insert_stmt.execute
conn.commit
# 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)}]"
end
# 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
function_stmt.execute_update
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
delete_stmt.execute_update
rescue
puts "\n** Error occured **\n"
puts "Failed executing Oracle demo from JRuby ", $!, "\n"
ensure
# We have to ensure everything is closed here.
if (!insert_stmt.nil?)
insert_stmt.close
end
if (!select_stmt.nil?)
select_stmt.close
end
if (!rset.nil?)
rset.close
end
if (!delete_stmt.nil?)
delete_stmt.close
end
if (!function_stmt.nil?)
function_stmt.close
end
conn.close_connection
end
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...
