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