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.

jdbc:oracle:thin:@apctcsol1.au.oracle.com:1521/pas_srv

In the example below we use some JRuby code to do the exact same thing. We are using the 11.2.0.2 Oracle JDBC Driver and an 11.2.0.2 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'

INSTANCE_SQL = <<EOF
select sys_context('userenv', 'instance_name'),
sys_context('userenv', 'server_host'), 
sys_context('userenv', 'service_name')
from dual
EOF

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

  def get_connection()
    @ods.get_connection
  end
  
  def create_statement()
    @connection.create_statement
  end
  
  def to_s  
    "OracleDataSource  [user=#{@user}, passwd=#{@passwd}, " +  
    "url=#{@url}]"  
  end  

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


user = "scott"
passwd = "tiger"
url = "jdbc:oracle:thin:@apctcsol1.au.oracle.com:1521/pas_srv"

print "Run at #{Time.now} 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
end

# 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 "============="    
  end
  
  stmt = conns[i].create_statement
  rset = stmt.execute_query INSTANCE_SQL
  rset.next
  puts "Connection #{i} : instance [#{rset.get_string 1}], " + 
       "host[#{rset.get_string 2}], service[#{rset.get_string 3}] "
  
  rset.close
  stmt.close
end

#close the 5 connections
for i in 1..5
  conns[i].close
end

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

Output

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

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 (11.1.1.4) 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 'pas.au.wsclient.SimpleWSPortClient'

class TestWLSWebService

  def initialize
    @wsclient = SimpleWSPortClient.new  
  end
  
  def invoke_method
    return @wsclient.invoke_method
  end
  
end


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

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

test = TestWLSWebService.new
print test.invoke_method

print "\n\nEnded at #{Time.now} \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

package pas.au.ws;

import java.util.Date;

import javax.jws.WebService;

@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(),
                    System.getProperty("weblogic.Name"));
    
    return retData;
    
  }
}

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 (11.2.0.2)

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 = OracleDriver.new
    
    DriverManager.registerDriver(oradriver)
    @conn = DriverManager.getConnection(url,user,passwd);
    @conn.setAutoCommit(false);
    
  end
 
  #add getters and setters for all attrributes we wish to expose
  attr_reader :user, :passwd, :url
 
  def getConnection()
    return @conn
  end

  def closeConnection()
    @conn.close()
  end

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

- 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;" );
  end
  
  def enable (size)
    @enable_stmt.setInt( 1, size );
    @enable_stmt.executeUpdate();
  end
  
  def disable
    @disable_stmt.executeUpdate();
  end
  
  def show
      output = ""
      done = 0;
  
      @show_stmt.registerOutParameter( 2, 4);
      @show_stmt.registerOutParameter( 3, 12 );
  
      while (done == 0)   
          @show_stmt.setInt( 1, 32000 );
          @show_stmt.executeUpdate();
          output = output , "" , @show_stmt.getString(3), "\n"
          if ( (done = @show_stmt.getInt(2)).to_i == 1 ) 
            break
          end
      end
      
      return output
  end
  
  def closeAll
    @enable_stmt.close();
    @disable_stmt.close();
    @show_stmt.close();
  end
  
end 

- 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 ", Time.now , " using JRuby ", RUBY_VERSION, "\n"
puts 

conn = nil

begin

  conn = OracleConnection.new("scott", "tiger", "jdbc:oracle:thin:@beast.au.oracle.com:1524/linux11gr2")
  puts conn
  dbms_output = DbmsOutput.new(conn)
  dbms_output.enable( 1000000 );
  
  plsql_block = 
    "begin " + 
    " for i in 1..10 loop " +
    "  dbms_output.put_line('Hello JRuby at position '||i); " + 
    " end loop; " +
    "end;"
 
  stmt = conn.getConnection.createStatement();
  stmt.execute(plsql_block);   
  
  print "** Output from PLSQL Block as follows **"
  puts dbms_output.show()
  dbms_output.closeAll()
  
rescue 
  print "\n** Error occured **\n"
 print "Failed executing Oracle JDBC DBMS_OUTPUT demo from JRuby ", $!, "\n"
  if (!conn.nil?)
    conn.closeConnection()
  end
  
end

print "\nEnded at ", Time.now , "\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 = OracleConnection.new("scott", "tiger", "jdbc:oracle:thin:@beast.au.oracle.com:1524/linux11gr2")

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, url=jdbc:oracle:thin:@beast.au.oracle.com:1524/linux11gr2]
** 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

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 (11.2.0.2)
ucp.jar (11.2.0.1)

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'

API_VERSION = 1.0

class MyOracleUcpPool

  #pool data source object
  @pds
  
  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()
    @pds.setUser(user)
    @pds.setPassword(passwd)
    @pds.setURL(url)
    @pds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource")
    @pds.setConnectionPoolName("ruby-ucppool")
    @pds.setInitialPoolSize(initialsize)
    @pds.setMinPoolSize(minsize)
    @pds.setMaxPoolSize(maxsize)
    
  end
  
  #add getters and setters for all attrributes
  attr_reader :user, :passwd, :url, :minsize, :maxsize, :initialsize
  
  def getConnection()
    return @pds.getConnection()
  end
  
  def returnConnection(conn)
    conn.close()
  end
  
  def displayPoolDetails()
    return "\n** UCP Pool Details **\n" + 
            "NumberOfAvailableConnections: ", @pds.getAvailableConnectionsCount(), "\n" +
            "BorrowedConnectionsCount: ", @pds.getBorrowedConnectionsCount(), " \n";
  end
  
  def to_s
    "MyOracleUcpPool [user=#{@user}, passwd=#{@passwd}, " +
    "url=#{@url}, minsize=#{@minsize}, maxsize=#{@maxsize}, " +
    "initialsize=#{@initialsize}]"
  end
  alias_method :to_string, :to_s
  
 end

- 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 ", Time.now , "\n"

conn = nil
ucppool = nil

begin

  ucppool = MyOracleUcpPool.new("hr", "hr", "jdbc:oracle:thin:@beast.au.oracle.com:1524/linux11gr2", 0, 5, 2)
  print ucppool , "\n"

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

  #print pool details
  print ucppool.displayPoolDetails()
  puts

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

  rset.close()
  stmt.close()

  #return connection
  ucppool.returnConnection(conn)
  print "\nConnection returned to pool\n"

  #print pool details
  print ucppool.displayPoolDetails()

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

print "\nEnded at ", Time.now , "\n"

4. Your file system would look as follows

C:\jdev\scripting\demos\jruby\jdbc\ucp>dir
 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 = MyOracleUcpPool.new("hr", "hr", "jdbc:oracle:thin:@beast.au.oracle.com:1524/linux11gr2", 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

OUTPUT

Run at Fri Jan 07 09:25:16 +1100 2011
MyOracleUcpPool [user=hr, passwd=hr, url=jdbc:oracle:thin:@beast.au.oracle.com:1524/linux11gr2, 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)
http://download.oracle.com/docs/cd/E11882_01/java.112/e12265/toc.htm