12 February 2013
Over the years, I have attempted PLSQL unit testing with some success using different tools, but they always left me frustrated. The best tool was probably utplsql, but the test code was so verbose it got annoying very quickly.
These days there seems to be a push toward doing PLSQL unit testing using a GUI (built into Toad and SQL Developer), which doesn't fill me with joy either.
I decided that Ruby would make a pretty good tool to test stored procedure calls, as it is easily able to execute the stored procedures and can query the database pretty effectively too. I started out with the Ruby OCI8 gem. At the time I quickly hit a problem, as it didn't support PLSQL array types, which I needed to test.
At that point I figured it would be nice if I could use the Oracle JDBC drivers along with JRuby and get my testing done that way. I quickly realized that the endless set_int, get_int and mapping between Java native types and Ruby native types was going to be tedious, and Simple Oracle JDBC was born.
The idea behind this gem is that it provides a thin wrapper around a JDBC connection. It provides an interface to quickly execute SQL statements and stored procedures, but leaves the raw JDBC connection available if anything more complicated is required, such as binding array types.
Values can be bound to SQL statements and procedures by simply passing an array of Ruby types into the execute call, and they are mapped automatically into the correct Java SQL types. The same happens when values are returned from procedures and queries.
While the gem was created to help me with Unit Testing PLSQL code, there is nothing preventing it being used for other quick scripts or prototypes. I haven't looked at performance at all, so if you decided to use it in a production application, test it thoroughly first!
The best way to learn how to use Simple Oracle JDBC is to read through the sample code below, and then checkout the documentation over at rubygems.org.
require 'simple_oracle_jdbc'
conn = SimpleOracleJDBC::Interface.create('sodonnell', # user
'sodonnell', # password
'tuned', # service
'192.168.0.1', # host
'1521') # port
# ... or create with an existing JDBC connection
# conn = SimpleOracleJDBC.create_with_existing_connection(conn)
# Create a SimpleOracleJDBC::SQL object
sql = conn.prepare_sql("select 1 c1, 'abc' c2, sysdate c3, 23.56 c4
from dual
where 1 = :b1
and 2 = :b2")
# execute the query against the database, passing any binds as required
sql.execute(1, 2)
# get the results back as an array of arrays. Note that the resultset
# and statement will be closed after this call, so the SQL cannot
# be executed again.
results = sql.all_array
puts "The returned row is #{results[0]}"
# > The returned row is [1.0, "abc", 2013-02-12 22:00:23 +0000, 23.56]
# Run the same SQL statement again
sql = conn.prepare_sql("select 1 c1, 'abc' c2, sysdate c3, 23.56 c4
from dual
where 1 = :b1
and 2 = :b2")
sql.execute(1, 2)
# This time fetch the results as an array of hashes
results = sql.all_hash
puts "The returned row is #{results[0]}"
puts results[0]["C3"].class
# Notice how the column names are the keys of the hash, and the date is converted
# into a Ruby Time object.
#
# > The returned row is {"C1"=>1.0, "C2"=>"abc", "C3"=>2013-02-12 22:03:02 +0000, "C4"=>23.56}
# > Time
# If you need to iterate over a large result set, then pass a block to the each_array
# or each_hash method
sql = conn.prepare_sql("select level rnum, 1 c1, 'abc' c2, sysdate c3, 23.56 c4
from dual
where 1 = :b1
and 2 = :b2
connect by level <= 4")
sql.execute(1, 2).each_hash do |row|
puts row
end
# > {"RNUM"=>1.0, "C1"=>1.0, "C2"=>"abc", "C3"=>2013-02-12 22:07:14 +0000, "C4"=>23.56}
# > {"RNUM"=>2.0, "C1"=>1.0, "C2"=>"abc", "C3"=>2013-02-12 22:07:14 +0000, "C4"=>23.56}
# > {"RNUM"=>3.0, "C1"=>1.0, "C2"=>"abc", "C3"=>2013-02-12 22:07:14 +0000, "C4"=>23.56}
# > {"RNUM"=>4.0, "C1"=>1.0, "C2"=>"abc", "C3"=>2013-02-12 22:07:14 +0000, "C4"=>23.56}
# Finally you can ask for each row one at a time, with each_array or each_hash
sql = conn.prepare_sql("select level rnum, 1 c1, 'abc' c2, sysdate c3, 23.56 c4
from dual
where 1 = :b1
and 2 = :b2
connect by level <= 4")
sql.execute(1, 2)
# If you fetch to the end of the result set, then the statement and
# and result set will be closed. Otherwise, call the close method:
#
# sql.close
while row = sql.next_array do
puts "The row is #{row}"
end
# > The row is [1.0, 1.0, "abc", 2013-02-12 22:11:38 +0000, 23.56]
# > The row is [2.0, 1.0, "abc", 2013-02-12 22:11:38 +0000, 23.56]
# > The row is [3.0, 1.0, "abc", 2013-02-12 22:11:38 +0000, 23.56]
# > The row is [4.0, 1.0, "abc", 2013-02-12 22:11:38 +0000, 23.56]
# Executing Stored Procedures is easy too, just take care of out and inout parameters.
#
# create or replace function test_func(i_var integer default null)
# return integer
# is
# begin
# if i_var is not null then
# return i_var;
# else
# return -1;
# end if;
# end;
# /
#
# Execute a function with a returned parameter. Notice how the
# out/returned parameter is passed as a 3 element array.
# The first element defines the Ruby type which is mapped into a SQL type as follows:
#
# RUBY_TO_JDBC_TYPES = {
# Date => OracleTypes::DATE,
# Time => OracleTypes::TIMESTAMP,
# String => OracleTypes::VARCHAR,
# Fixnum => OracleTypes::INTEGER,
# Integer => OracleTypes::INTEGER,
# Bignum => OracleTypes::NUMERIC,
# Float => OracleTypes::NUMERIC,
# :refcursor => OracleTypes::CURSOR
# }
#
# The second element is the value, which should be nil for out parameters and can take a
# value for inout parameters.
#
# The third parameter should always be :out
#
# Also notice how the value is retrieved using the [] method, which is indexed from 1 not zero.
# In, out and inout parameters can be accessed using the [] method.
proc = conn.prepare_proc("begin :return := test_func(); end;")
proc.execute([String, nil, :out])
puts "The returned value is #{proc[1]}"
# > The returned value is -1
# To pass parameters into the function, simply pass plain Ruby values:
proc = conn.prepare_proc("begin :return := test_func(:b1); end;")
proc.execute([String, nil, :out], 99)
puts "The returned value is #{proc[1]}"
proc.close
# > The returned value is 99
# A refcursor is returned from a stored procedure as a SimpleOracleJDBC::SQL object, so it can
# be accessed in the way as the SQL examples above:
#
# create or replace function test_refcursor
# return sys_refcursor
# is
# v_refc sys_refcursor;
# begin
# open v_refc for
# select level rnum, 1 c1, 'abc' c2, sysdate c3, 23.56 c4
# from dual
# connect by level <= 4;
#
# return v_refc;
# end;
# /
#
proc = conn.prepare_proc("begin :return := test_refcursor; end;")
proc.execute([:refcursor, nil, :out])
sql_object = proc[1]
sql_object.each_hash do |row|
puts row
end
proc.close
# > {"RNUM"=>1.0, "C1"=>1.0, "C2"=>"abc", "C3"=>2013-02-12 22:32:48 +0000, "C4"=>23.56}
# > {"RNUM"=>2.0, "C1"=>1.0, "C2"=>"abc", "C3"=>2013-02-12 22:32:48 +0000, "C4"=>23.56}
# > {"RNUM"=>3.0, "C1"=>1.0, "C2"=>"abc", "C3"=>2013-02-12 22:32:48 +0000, "C4"=>23.56}
# > {"RNUM"=>4.0, "C1"=>1.0, "C2"=>"abc", "C3"=>2013-02-12 22:32:48 +0000, "C4"=>23.56}