Using JRuby with Oracle Database

 

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
    Employee 200 is Jennifer Whalen
4. Deleting department 281 from the DEPARTMENTS table

Ended at Fri Feb 11 14:54:32 -0800 2011

If the test is changed to insert department 100 instead of 281, an error stack will be displayed:

** Error occurred **
Failed executing Oracle demo from JRuby
java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint (HR.DEPT_ID_PK) violated

 

Congratulations, you have just learned how to access an Oracle 11g Release 2 database using JRuby!