ray_apps_blog

March 15, 2008

ruby-plsql gem: simple Ruby API for PL/SQL procedures

Filed under: oracle, pl/sql, ruby — Raimonds Simanovskis @ 11:44 pm

In several projects I have used Ruby and Rails to access legacy Oracle databases which have both tables with data as well as PL/SQL packages with lot of existing business logic. Sometimes it is easier just to redo business logic in Ruby but sometimes you need to reuse existing PL/SQL packages and procedures.

Let’s use this simple PL/SQL function as an example:

CREATE OR REPLACE FUNCTION test_uppercase
  ( p_string VARCHAR2 )
  RETURN VARCHAR2
IS
BEGIN
  RETURN UPPER(p_string);
END test_uppercase;

If you are using ruby-oci8 library to connect to Oracle then you can call this PL/SQL procedure from Ruby in the following way (more details can be found in ruby-oci8 documentation):

conn = OCI8.new("hr","hr","xe")
cursor = conn.parse <<-EOS
BEGIN
  :return := test_uppercase(:p_string);
END;
EOS
cursor.bind_param(':p_string',"xxx",String)
cursor.bind_param(':return',nil,String,4000)
cursor.exec
puts cursor[':return']
cursor.close

This does not look like Ruby-style as it is too long and complex code which just calls one simple PL/SQL function :(

Wouldn’t it be much nicer if you could get the same result with the following code?

plsql.connection = OCI8.new("hr","hr","xe")
puts plsql.test_uppercase('xxx')

This idea served as inspiration to create ruby-plsql gem which would provide such nice Ruby API to access existing PL/SQL procedures and functions. Here are other examples how you can use it.

Call procedure with named parameters:

plsql.test_uppercase(:p_string => 'xxx')

Call procedure with specified schema:

plsql.hr.test_uppercase('xxx')

Call procedure from specified package in specified schema:

plsql.hr.test_package.test_uppercase('xxx')

Call procedure with output arguments:

plsql.test_copy("abc", nil, nil) # returns output arguments { :p_to => "abc", :p_to_double => "abcabc" }

To install this gem execute

sudo gem install ruby-plsql

As I mentioned before it also requires ruby-oci8 library to access Oracle. I have plans to create JRuby / JDBC support in the future versions of this gem.

Current limitation is that this API support just NUMBER, VARCHAR2, DATE and TIMESTAMP types for PL/SQL procedures which are dinamically mapped to Ruby Fixnum/Bignum/Float, String, DateTime and Time types.

If you find this gem interesting then please let me know in comments what additional features you would like to have for it.

2 Comments »

  1. Hi, Raimonds!

    Thank you very much for your gem! It’s very useful!

    I have tried it with Oracle 10g on Windows XP in a Parallels virtual machine and it works perfectly. Of course, I’d like to have support for more datatypes. Especially for collection types such as VARRAY.

    A tighter ActiveRecord integration would be great, too. ‘plsql.’ in front of every statement reads a bit awkward.

    Cheers,

    Maik

    Comment by Maik Schmidt — May 7, 2008 @ 7:45 pm

  2. I created Lighthouse project where to report ruby-plsql bugs and register feature requests - http://rsim.lighthouseapp.com/projects/11470-ruby-plsql/tickets

    Please use it if you are interested in ruby-plsql further development :)

    Comment by Raimonds Simanovskis — May 16, 2008 @ 9:46 pm

RSS feed for comments on this post.

Leave a comment

Blog at WordPress.com.