Monday, 31 January 2011

JRuby Oracle RAC 11g R2 SCAN Client Demo

Previously I created a JDBC client against an 11g R2 RAC client using SCAN as shown here. That example simply ensures we are load balancing between the RAC nodes in the cluster because from the JDBC URL we would not even know we are connecting to a RAC cluster.

In the example below we use some JRuby code to do the exact same thing. We are using the Oracle JDBC Driver and an RAC cluster as well.

JRuby Code

require 'java'  
require 'C:/jdev/jdcbdrivers/11.2/11202/ojdbc6.jar'  
java_import java.sql.Statement
java_import java.sql.Connection
java_import java.sql.SQLException
java_import java.sql.DatabaseMetaData

java_import 'oracle.jdbc.OracleDriver'
java_import 'oracle.jdbc.pool.OracleDataSource'

select sys_context('userenv', 'instance_name'),
sys_context('userenv', 'server_host'), 
sys_context('userenv', 'service_name')
from dual

class MyOracleDataSource   
  def initialize(user, passwd, url)  
    @user, @passwd, @url = user, passwd, url
    @ods =
    @ods.set_user user
    @ods.set_password passwd
    @ods.set_url url
  # add getters and setters for all attrributes we wish to expose  
  attr_reader :user, :passwd, :url

  def get_connection()
  def create_statement()
  def to_s  
    "OracleDataSource  [user=#{@user}, passwd=#{@passwd}, " +  

  def self.create(user, passwd, url)
    ods = new(user, passwd, url)
    puts "\n** Error occured **\n"  
    puts "Failed executing SCAN Load Blance test from JRuby ", $!, "\n"

user = "scott"
passwd = "tiger"
url = ""

print "Run at #{} using JRuby #{RUBY_VERSION}\n\n"  

mods = MyOracleDataSource.create(user, passwd, url)

# empty array
conns = []

# obtain 5 connections
for i in 1..5 
  conns[i] = mods.get_connection

# determine instance details
for i in 1..5
  if (i == 1)
    meta = conns[i].get_meta_data
    puts "=============\nDatabase Product Name is ... #{meta.getDatabaseProductName()}"  
    puts "Database Product Version is  #{meta.getDatabaseProductVersion()}"  
    puts "=============\nJDBC Driver Name is ........ #{meta.getDriverName()}" 
    puts "JDBC Driver Version is ..... #{meta.getDriverVersion()}"
    puts "JDBC Driver Major Version is #{meta.getDriverMajorVersion()}"  
    puts "JDBC Driver Minor Version is #{meta.getDriverMinorVersion()}" 
    puts "============="    
  stmt = conns[i].create_statement
  rset = stmt.execute_query INSTANCE_SQL
  puts "Connection #{i} : instance [#{rset.get_string 1}], " + 
       "host[#{rset.get_string 2}], service[#{rset.get_string 3}] "

#close the 5 connections
for i in 1..5

print "\nEnded at #{}"


C:\jdev\scripting\demos\jruby\jdbc\scan-load-balance-test>jruby scan-lbt.rb
Run at Mon Jan 31 14:28:11 +1100 2011 using JRuby 1.8.7

Database Product Name is ... Oracle
Database Product Version is  Oracle Database 11g Enterprise Edition Release - Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
JDBC Driver Name is ........ Oracle JDBC driver
JDBC Driver Version is .....
JDBC Driver Major Version is 11
JDBC Driver Minor Version is 2
Connection 1 : instance [A12], host[auw2k4], service[pas_srv]
Connection 2 : instance [A11], host[auw2k3], service[pas_srv]
Connection 3 : instance [A12], host[auw2k4], service[pas_srv]
Connection 4 : instance [A11], host[auw2k3], service[pas_srv]
Connection 5 : instance [A12], host[auw2k4], service[pas_srv]

Ended at Mon Jan 31 14:28:12 +1100 2011

Friday, 21 January 2011

Invoking a JAX-WS Oracle Weblogic 11g (10.3.4) Web Service from JRuby

The following demo show how to call a JAX-WS from Oracle Weblogic Server 11g (10.3.4) from a JRuby script. The real work is done by JDeveloper 11g ( which creates the Web Service, generates a proxy client JAR which JRuby then uses to invoke the Web Service.

So in JDeveloper we have 2 projects one for the Web Service and one for the Proxy.

The Web Service is deployed to Weblogic 10.3.4 as shown below.

1. Create a JRuby script named "jaxws11g.rb" as follows
require 'java'

# the web service client JAR file generated from JDeveloper 11g Proxy wizard
require 'SimpleWSService-Client.jar'

java_import ''

class TestWLSWebService

  def initialize
    @wsclient =  
  def invoke_method
    return @wsclient.invoke_method

print "Run at #{} using JRuby #{RUBY_VERSION}\n\n"

print "** FMW Weblogic 10.3.4 Web Service Invoke Test **\n\n"

test =
print test.invoke_method

print "\n\nEnded at #{} \n"

2. The JAR file referenced below is the Web Service proxy JAR file which is generated by JDeveloper once a proxy project is created from the WSDL file for the Web Service.

# the web service client JAR file generated from JDeveloper 11g Proxy wizard
require 'SimpleWSService-Client.jar'

3. Run shown below which simply identified the Weblogic managed server name it's running in and the date it was invoked.

> jruby jaxws11g.rb

Run at Fri Jan 21 11:10:37 +1100 2011 using JRuby 1.8.7
** FMW Weblogic 10.3.4 Web Service Invoke Test **
SimpleWS invoked at Thu Jan 20 11:05:13 EST 2011 from Weblogic Managed Server named apple
Ended at Fri Jan 21 11:10:38 +1100 2011

The Web Service is a simple class as follows


import java.util.Date;

import javax.jws.WebService;

public class SimpleWS 
  public SimpleWS() 
  public String getManagedServerName () 
    String retData = 
      String.format("SimpleWS invoked at %s from Weblogic Managed Server named %s", 
                    new Date(),
    return retData;

Thursday, 13 January 2011

Retrieving DBMS_OUTPUT from PLSQL in a JRuby script

This demo shows how to get DBMS OUTPUT form PLSQL blocks and PLSQL program units using JRuby. In this example we obtain a JDBC connection which we then use to invoke a PLSQL block and get the DBMS OUTPUT back to the JRuby script.

1. Download ojdbc6.jar from the links below.

ojdbc6.jar (

2. Create a JRuby script for the JDBC connection named "jdbc_connection.rb" as follows

require 'java'
require 'C:/jdev/jdcbdrivers/11.2/ojdbc6.jar'

import 'oracle.jdbc.OracleDriver'
import 'java.sql.DriverManager'

class OracleConnection 

  @conn = nil
  def initialize (user, passwd, url)
    @user = user
    @passwd = passwd
    @url = url
    # load driver class
    oradriver =
    @conn = DriverManager.getConnection(url,user,passwd);
  #add getters and setters for all attrributes we wish to expose
  attr_reader :user, :passwd, :url
  def getConnection()
    return @conn

  def closeConnection()

  def to_s
    "OracleConnection [user=#{@user}, passwd=#{@passwd}, " +
  alias_method :to_string, :to_s

- Edit the line below to indicate the PATH to the Oracle JDBC driver downloaded at step #1

require 'C:/jdev/jdcbdrivers/11.2/ojdbc6.jar'

3. Create a JRuby script for the DBMS OUTPUT class named "dbms_output.rb" as follows.
require 'java'

require 'C:/jdev/jdcbdrivers/11.2/ojdbc6.jar'

import 'java.sql.CallableStatement'
import 'java.sql.Connection'
import 'java.sql.SQLException'
import 'java.sql.Types'

class DbmsOutput

  def initialize (conn)
    @enable_stmt  = conn.getConnection.prepareCall( "begin dbms_output.enable(:1); end;" );
    @disable_stmt = conn.getConnection.prepareCall( "begin dbms_output.disable; end;" );
    @show_stmt = conn.getConnection.prepareCall( 
          "declare " +
          "    l_line varchar2(255); " +
          "    l_done number; " +
          "    l_buffer long; " +
          "begin " +
          "  loop " +
          "    exit when length(l_buffer)+255 > :maxbytes OR l_done = 1; " +
          "    dbms_output.get_line( l_line, l_done ); " +
          "    l_buffer := l_buffer || l_line || chr(10); " +
          "  end loop; " +
          " :done := l_done; " +
          " :buffer := l_buffer; " +
          "end;" );
  def enable (size)
    @enable_stmt.setInt( 1, size );
  def disable
  def show
      output = ""
      done = 0;
      @show_stmt.registerOutParameter( 2, 4);
      @show_stmt.registerOutParameter( 3, 12 );
      while (done == 0)   
          @show_stmt.setInt( 1, 32000 );
          output = output , "" , @show_stmt.getString(3), "\n"
          if ( (done = @show_stmt.getInt(2)).to_i == 1 ) 
      return output
  def closeAll

- Edit the line below to indicate the PATH to the Oracle JDBC driver downloaded at step #1

require 'C:/jdev/jdcbdrivers/11.2/ojdbc6.jar'

4. Create a JRuby test script named "test_dbms_output.rb" as follows
# test DBMS_OUTPUT from JRuby

require 'lib/jdbc_connection'
require 'lib/dbms_output'

print "Run at ", , " using JRuby ", RUBY_VERSION, "\n"

conn = nil


  conn ="scott", "tiger", "")
  puts conn
  dbms_output =
  dbms_output.enable( 1000000 );
  plsql_block = 
    "begin " + 
    " for i in 1..10 loop " +
    "  dbms_output.put_line('Hello JRuby at position '||i); " + 
    " end loop; " +
  stmt = conn.getConnection.createStatement();
  print "** Output from PLSQL Block as follows **"
  print "\n** Error occured **\n"
 print "Failed executing Oracle JDBC DBMS_OUTPUT demo from JRuby ", $!, "\n"
  if (!conn.nil?)

print "\nEnded at ", , "\n"

- Edit the 2 lines above to refer to the files created at step #2 and step #3. In this example they exist of a lib directory from the current directory.

require 'lib/jdbc_connection'
require 'lib/dbms_output'

Note: Ensure you specify a connection to your database as follows

conn ="scott", "tiger", "")

5. Run test_dbms_output.rb

Run at Thu Jan 13 07:45:21 +1100 2011 using JRuby 1.8.7

OracleConnection [user=scott, passwd=tiger,]
** Output from PLSQL Block as follows **

Hello JRuby at position 1
Hello JRuby at position 2
Hello JRuby at position 3
Hello JRuby at position 4
Hello JRuby at position 5
Hello JRuby at position 6
Hello JRuby at position 7
Hello JRuby at position 8
Hello JRuby at position 9
Hello JRuby at position 10

Ended at Thu Jan 13 07:45:21 +1100 2011

Friday, 7 January 2011

JRuby example using Oracle UCP (Universal Connection Pool)

The Oracle Universal Connection Pool (UCP) for JDBC is a full-featured connection pool for managing database connections not only for Oracle but also for other non Oracle databases. The advantage with using UCP with Oracle is that UCP JDBC connection pools provide a tight integration with various Oracle Real Application Clusters (RAC) Database features . The features include Fast Connection Failover (FCF), Run-Time Connection Load Balancing, and Connection Affinity.

In this example below we show how to use UCP with an Oracle Database from a JRuby client BUT just a single instance database rather then RAC back end.

Note: We are using the HR schema for this demo

1. Download ucp.jar and ojdbc6.jar from the links below.

ojdbc6.jar (
ucp.jar (

2. Create a pool class as follows in a JRuby script called "ucppool.rb" in a directory called "lib". Client would then create an instance of this pool class and use it to retrieve / return connections from it.

require 'java'
require 'C:/jdev/jdcbdrivers/11.2/ojdbc6.jar'
require 'C:/jdev/jdcbdrivers/11.2/ucp/ucp.jar'

import 'oracle.ucp.jdbc.PoolDataSource'
import 'oracle.ucp.jdbc.PoolDataSourceFactory'


class MyOracleUcpPool

  #pool data source object
  def initialize(user, passwd, url, minsize=0, maxsize=10, initialsize=2)
    @user = user
    @passwd = passwd
    @url = url
    @minsize = minsize
    @maxsize = maxsize
    @initialsize = initialsize
    #create pool for use here
    @pds = PoolDataSourceFactory.getPoolDataSource()
  #add getters and setters for all attrributes
  attr_reader :user, :passwd, :url, :minsize, :maxsize, :initialsize
  def getConnection()
    return @pds.getConnection()
  def returnConnection(conn)
  def displayPoolDetails()
    return "\n** UCP Pool Details **\n" + 
            "NumberOfAvailableConnections: ", @pds.getAvailableConnectionsCount(), "\n" +
            "BorrowedConnectionsCount: ", @pds.getBorrowedConnectionsCount(), " \n";
  def to_s
    "MyOracleUcpPool [user=#{@user}, passwd=#{@passwd}, " +
    "url=#{@url}, minsize=#{@minsize}, maxsize=#{@maxsize}, " +
  alias_method :to_string, :to_s

- Edit the 2 lines below to indicate the PATH to the 2 JAR files downloaded at step #1

require 'C:/jdev/jdcbdrivers/11.2/ojdbc6.jar'
require 'C:/jdev/jdcbdrivers/11.2/ucp/ucp.jar'

3. In your current directory create a test client to verify the UCP called "testucp.rb" as follows to test your UCP Pool.

require 'lib/ucppool'

print "Run at ", , "\n"

conn = nil
ucppool = nil


  ucppool ="hr", "hr", "", 0, 5, 2)
  print ucppool , "\n"

  #get connection from pool
  conn = ucppool.getConnection()

  #print pool details
  print ucppool.displayPoolDetails()

  #execute query
  stmt = conn.createStatement()
  rset = stmt.executeQuery("select * from departments")
    print "DepartmentId=", rset.getInt(1), 
          ", DepartmentName=" + rset.getString(2), "\n"


  #return connection
  print "\nConnection returned to pool\n"

  #print pool details
  print ucppool.displayPoolDetails()

  print "\n** Error occured **\n"
 print "Failed executing UCP Pool demo from JRuby ", $!, "\n"
  if (!conn.nil?)
    if (!ucppool.nil?)
      print "\nConnection returned to pool\n"

print "\nEnded at ", , "\n"

4. Your file system would look as follows
 Volume in drive C is OS
 Volume Serial Number is 7C37-0C64

 Directory of C:\jdev\scripting\demos\jruby\jdbc\ucp

06/01/2011  10:21 PM    <DIR>          .
06/01/2011  10:21 PM    <DIR>          ..
06/01/2011  10:30 PM    <DIR>          lib
07/01/2011  08:47 AM             1,112 testucp.rb
               1 File(s)          1,112 bytes
               3 Dir(s)  248,428,380,160 bytes free

5. Edit the following line of code to ensure you provide the correct connect details for your database.

ucppool ="hr", "hr", "", 0, 5, 2)

6. Run the test client using jruby testucp.rb. In this example we do the following.

  • create a UCP Pool
  • obtain a JDBC Connection from the Pool
  • Query a table
  • Return the JDBC connection to the pool


Run at Fri Jan 07 09:25:16 +1100 2011
MyOracleUcpPool [user=hr, passwd=hr,, minsize=0, maxsize=5, initialsize=2]

** UCP Pool Details **
NumberOfAvailableConnections: 1
BorrowedConnectionsCount: 1

DepartmentId=10, DepartmentName=Administration
DepartmentId=20, DepartmentName=Marketing
DepartmentId=30, DepartmentName=Purchasing
DepartmentId=40, DepartmentName=Human Resources
DepartmentId=50, DepartmentName=Shipping
DepartmentId=60, DepartmentName=IT
DepartmentId=70, DepartmentName=Public Relations
DepartmentId=80, DepartmentName=Sales
DepartmentId=90, DepartmentName=Executive
DepartmentId=100, DepartmentName=Finance
DepartmentId=110, DepartmentName=Accounting
DepartmentId=120, DepartmentName=Treasury
DepartmentId=130, DepartmentName=Corporate Tax
DepartmentId=140, DepartmentName=Control And Credit
DepartmentId=150, DepartmentName=Shareholder Services
DepartmentId=160, DepartmentName=Benefits
DepartmentId=170, DepartmentName=Manufacturing
DepartmentId=180, DepartmentName=Construction
DepartmentId=190, DepartmentName=Contracting
DepartmentId=200, DepartmentName=Operations
DepartmentId=210, DepartmentName=IT Support
DepartmentId=220, DepartmentName=NOC
DepartmentId=230, DepartmentName=IT Helpdesk
DepartmentId=240, DepartmentName=Government Sales
DepartmentId=250, DepartmentName=Retail Sales
DepartmentId=260, DepartmentName=Recruiting
DepartmentId=270, DepartmentName=Payroll

Connection returned to pool

** UCP Pool Details **
NumberOfAvailableConnections: 2
BorrowedConnectionsCount: 0

Ended at Fri Jan 07 09:25:16 +1100 2011

More Information

Oracle Universal Connection Pool for JDBC Developer's Guide
11g Release 2 (11.2)