I have released ruby-plsql gem (Ruby API for Oracle PL/SQL procedure calls) new version 0.3.0 which includes several new features.
Ruby 1.9.1
Probably the most important is support for Ruby 1.9.1 – now you can use both Oracle enhanced adapter and ruby-plsql gem on all three major Ruby plaforms:
- MRI 1.8.6 with ruby-oci8 1.0.x library or gem
- Ruby/YARV 1.9.1 with ruby-oci8 2.0 library or gem (currently just trunk version of ruby-oci8 2.0 contains the last bug fixes for Ruby 1.9.1)
- JRuby (so far tested with 1.1.6) with JDBC Oracle driver
ActiveRecord connection
In addition usage of ruby-plsql gem in Ruby on Rails project is simplified. Now you can include in environment.rb or some initializer file just:
plsql.activerecord_class = ActiveRecord::Base
and you don’t need to specify plsql.connection anymore – it will always use current ActiveRecord connection. This is also useful when ActiveRecord reestablishes connection to database as you don’t need to reestablish plsql connection in this case.
In addition if you use several different connections to Oracle database then you can assign to plsql.activerecord_class also different class that inherits from ActiveRecord::Base and has connection to different database.
Database time zone
Also you can also specify in which timezone DATE values are stored in database:
plsql.default_timezone = :local
or
plsql.default_timezone = :utc
This will affect how DATE values (without timezone) will be converted to Time or DateTime values (with timezone), default selection is :local timezone. If you have set plsql.activerecord_class then the value will be taken from ActiveRecord::Base.default_timezone.
BLOB support
You can now use BLOB data type for input and output parameters and function return values.
I remind you that also NUMBER, VARCHAR2, DATE, TIMESTAMP and CLOB data types are supported,
Synonym support
Now you can also use private and public database synonyms to functions or procedures or packages.
E.g. if ORA_LOGIN_USER is public database synonym to SYS.LOGIN_USER function then instead of
plsql.sys.login_user
you can use
plsql.ora_login_user
Installation
To install the gem as always do
sudo gem install ruby-plsql
or call the correct gem command version for JRuby or Ruby 1.9.1.
Source code of ruby-plsql is located at GitHub where you can find usage examples in RSpec tests.
I’ve enjoyed reading your blog. Most are full of garbage, but this is really interesting – and useful too!
Comment by bank register — May 22, 2009 @ 10:51 pm |
They say that when buying a house the three key factors are location, location, location. Well, when looking for a useful website the factors are quality,quality,quality. The content here fits the bill exactly. Thanks a bunch. Great stuff!
Comment by leather checkbook — May 23, 2009 @ 1:03 pm |
Hi Raimonds, thankyou – that plsql gem is AWESOME and makes calling oracle stored procs really easily. Could you please let me know if it supports refcursors.
I am trying to call a procedure that returns a refcursor and cant get the call syntax correct (using jruby)
Note: the stored proc is like…
procedure pkg.test ( p_cursor out sys_refcursor)
is
begin
open p_cursor for
select
col1 ,
col2
from pkg.table1;
end test;
Comment by Marcus — July 23, 2009 @ 7:03 am |
Currently I do not support Oracle cursors as IN or OUT parameters.
It is a little bit tricky as there is no standard Ruby type that I could map returned SYS_REFCURSOR to.
If I could think of some good Ruby syntax for this case then I could try to implement it in future ruby-plsql versions.
How would you like to use returned cursor in Ruby? Would you like to fetch all rows from it or fetch one by one?
Comment by Raimonds Simanovskis — July 23, 2009 @ 10:21 am |
perhaps something like
psql.pkg.test {|row| row.each {|col| }
I guess an enhanced version could support batch fetching similar to find_in_batches.
I have managed to call using a ref-cursor using the following jdbc code – not a generic solution and only for JRuby but found no references on google so i will paste it here….
def test
conn = ActiveRecord::Base.connection.raw_connection
cstmt = conn.prepareCall(“begin test(?); end;”)
cstmt.registerOutParameter(1, -10) # -10 == oracle.jdbc.driver.OracleTypes.CURSOR
cstmt.execute
rs = cstmt.getObject(1)
rsm = rs.getMetaData
columnCount = rsm.getColumnCount
rows = []
while (rs.next())
row = []
for j in (1..columnCount)
row << rs.getObject(j)
end
rows << row
end
return rows
ensure
rs.close if rs
cstmt.close if cstmt
end
Comment by Marcus Baguley — July 24, 2009 @ 12:30 am |
hi Raimonds,
I am very happy user of your gem.
I encountered today an obstacle when handling 2 connections in one script.
I have 2 databases emeadb11 and mktgcd
chris@chris-ub:~$ irb
irb(main):001:0> require ‘rubygems’
=> true
irb(main):002:0> require ‘ruby_plsql’
=> true
irb(main):003:0> con1 = OCI8.new(‘chris’,'*****’,'emeadb11′)
=> #
irb(main):004:0> con2 = OCI8.new(‘kcierpisz’,'*****’,'mktgcd’)
=> #
irb(main):005:0> plsql.connection = con1
=> #
irb(main):006:0> plsql
=> #<PLSQL::Schema:0x8cd9f94 @schema_name=nil, @procedures={}, @connection=#<PLSQL::OCIConnection:0x8cd95a8 @activerecord_class=nil, @raw_connection=#, @raw_driver=:oci>, @first=true, @schemas={}, @packages={}>
irb(main):007:0> plsql.emeadb_phone_pkg.prepare_phones_in(‘EMEA_PHONES’)
=> nil
irb(main):008:0> plsql
=> #<PLSQL::Schema:0x8cd9f94 @schema_name="CHRIS", @procedures={}, @connection=#<PLSQL::OCIConnection:0x8cd95a8 @activerecord_class=nil, @raw_connection=#, @raw_driver=:oci>, @first=true, @schemas={}, @packages={:emeadb_phone_pkg=>##[]}, @schema=#, @arguments={0=>{:emea_phones_table=>{:data_precision=>nil, :data_type=>”VARCHAR2″, :data_scale=>nil, :in_out=>”IN”, :data_length=>nil, :position=>1}}}, @argument_list={0=>[:emea_phones_table]}, @overloaded=false, @package=”EMEADB_PHONE_PKG”, @return={0=>nil}, @overloads=[0], @procedure=”PREPARE_PHONES_IN”>}, @override_schema_name=nil, @schema=#, @package=”EMEADB_PHONE_PKG”>}>
irb(main):009:0> plsql.connection = con2
=> #
irb(main):010:0> plsql
=> #<PLSQL::Schema:0x8cd9f94 @schema_name="CHRIS", @procedures={}, @connection=#<PLSQL::OCIConnection:0x97554c8 @activerecord_class=nil, @raw_connection=#, @raw_driver=:oci>, @first=true, @schemas={}, @packages={}>
irb(main):011:0> plsql.phone_pkg.create_table(‘EMEA_PHONES_TMP’)
ArgumentError: No PL/SQL procedure found
from /home/chris/.gem/ruby/1.8/gems/ruby-plsql-0.3.1/lib/plsql/schema.rb:127:in `method_missing’
from (irb):11
from :0
irb(main):012:0>
as you can see at irb(main):006 schema_name is nil (although it should already here have CHRIS I guess)
at irb(main):008 schema_name has value ‘CHRIS’ (probably because at irb(main):007 I ran a procedure)
at irb(main):009 I am assigning con2 (KCIERPISZ schema)
plsql though sees still CHRIS under schema_name
and that’s why irb(main):011 fails.
When I run only one connection per script everything is fine:
irb(main):001:0> require ‘rubygems’
=> true
irb(main):002:0> require ‘ruby_plsql’
=> true
irb(main):003:0>
irb(main):004:0* con2 = OCI8.new(“kcierpisz”,”*****”,”mktgcd”)
=> #
irb(main):005:0> plsql.connection = con2
=> #
irb(main):006:0> plsql.phone_pkg.create_table(‘EMEA_PHONES_TMP’)
=> nil
irb(main):007:0> plsql
=> #<PLSQL::Schema:0x8f33984 @schema_name="KCIERPISZ", @procedures={}, @connection=#<PLSQL::OCIConnection:0x8f338e4 @activerecord_class=nil, @raw_connection=#, @raw_driver=:oci>, @first=true, @schemas={}, @packages={:phone_pkg=>##[]}, @schema=#, @arguments={0=>{:emea_phones_tmp_table=>{:data_precision=>nil, :data_type=>”VARCHAR2″, :data_scale=>nil, :in_out=>”IN”, :data_length=>nil, :position=>1}}}, @argument_list={0=>[:emea_phones_tmp_table]}, @overloaded=false, @package=”PHONE_PKG”, @return={0=>nil}, @overloads=[0], @procedure=”CREATE_TABLE”>}, @override_schema_name=nil, @schema=#, @package=”PHONE_PKG”>}>
irb(main):008:0>
Is there a way I could overwrite that schema_name (if that causes the issue)?
thanks a lot for your help,
chris
Comment by chris — October 22, 2009 @ 9:06 pm |
Fixed the issue – I was not clearing cached schema name after resetting connection. Thanks for reporting :)
I pushed the patch to github – if this is critical for you then please clone it from github.com/rsim/ruby-plsql and run “rake install_gem”. Or you can wait until I publish new version of gem.
Btw if you need to operate with two different connections then you can use them in parallel like:
plsql(:one).connection = OCI8.new(’chris’,’*****’,’emeadb11′)
plsql(:two).connection = OCI8.new(’kcierpisz’,’*****’,’mktgcd’)
plsql(:one).emeadb_phone_pkg.prepare_phones_in(’EMEA_PHONES’)
plsql(:two).phone_pkg.create_table(’EMEA_PHONES_TMP’)
And also if you have any further issues with ruby-plsql then please use discussion group http://groups.google.com/group/oracle-enhanced – it is primarily used for oracle_enhanced adapter but ruby-plsql questions might be interesting for oracle_enhanced users as well.
Comment by Raimonds Simanovskis — October 23, 2009 @ 2:41 pm |
I cloned git repo, but rake install_gem did not work giving me following:
chris@chris-ub:~/src/ruby-plsql$ rake install_gem
(in /home/chris/src/ruby-plsql)
rm -r doc
rm -r pkg
rake aborted!
Don’t know how to build task ‘lib/ruby_plsql/version.rb’
I tried manually just add the line @schema_name = nil to schema.rb but it did not change the behaviour (maybe in between there were other changes, I see the directory structure is changed slightly inside of lib/)
I will wait for the gem. And anyway it’s not critical since the parallel connections option works great.
thanks for your great work
PS. from next issue on will post to the discussion group :)
Comment by chris — October 23, 2009 @ 8:45 pm
I didn’t update Manifest.txt file to reflect file name changes in gem – now rake install_gem should work.
Comment by Raimonds Simanovskis — October 26, 2009 @ 2:30 pm
and it does indeed, thanks :)
Comment by chris — October 26, 2009 @ 9:58 pm |