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
