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.
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 |
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 |
Hi Raimonds,
Thank you so much for ruby_plsql, it should be a very useful tool for me. However, I am having a problem getting it going at the moment. I wondered if you could help? I am using rails 2.1.0), activerecord (2.1.0), ActiveRecord-JDBC (0.5), activerecord-jdbc-adapter (0.9) and ruby-plsql (0.2.4).
I have placed the following at the top of my rails controller :
require “ruby_plsql”
plsql.connection = ActiveRecord::Base.connection.connection
I get the following error page when accesing the controller :
You have a nil object when you didn’t expect it!
You might have expected an instance of Array.
The error occurred while evaluating nil.split
C:/Dev/jruby/lib/ruby/gems/1.8/gems/ruby-plsql-0.2.4/lib/ruby_plsql.rb:23
C:/Dev/jruby/lib/ruby/gems/1.8/gems/ruby-plsql-0.2.4/lib/ruby_plsql.rb:36:in `require’
c:/Dev/jruby/lib/ruby/site_ruby/1.8/rubygems/custom_require.rb:36:in `require’
C:/Dev/jruby/lib/ruby/gems/1.8/gems/activesupport-2.1.0/lib/active_support/dependencies.rb:509:in `require’
C:/Dev/jruby/lib/ruby/gems/1.8/gems/activesupport-2.1.0/lib/active_support/dependencies.rb:354:in `new_constants_in’
C:/Dev/jruby/lib/ruby/gems/1.8/gems/activesupport-2.1.0/lib/active_support/dependencies.rb:509:in `require’
app/controllers/locations_controller.rb:1
app/controllers/locations_controller.rb:215:in `load’
:1:in `start’
C:/Dev/jruby/lib/ruby/gems/1.8/gems/ruby-plsql-0.2.4/lib/ruby_plsql.rb:23
C:/Dev/jruby/lib/ruby/gems/1.8/gems/ruby-plsql-0.2.4/lib/ruby_plsql.rb:36:in `require’
c:/Dev/jruby/lib/ruby/site_ruby/1.8/rubygems/custom_require.rb:36:in `require’
C:/Dev/jruby/lib/ruby/gems/1.8/gems/activesupport-2.1.0/lib/active_support/dependencies.rb:509:in `require’
C:/Dev/jruby/lib/ruby/gems/1.8/gems/activesupport-2.1.0/lib/active_support/dependencies.rb:354:in `new_constants_in’
C:/Dev/jruby/lib/ruby/gems/1.8/gems/activesupport-2.1.0/lib/active_support/dependencies.rb:509:in `require’
C:/Dev/jruby/lib/ruby/gems/1.8/gems/activesupport-2.1.0/lib/active_support/dependencies.rb:215:in `load_file’
C:/Dev/jruby/lib/ruby/gems/1.8/gems/activesupport-2.1.0/lib/active_support/dependencies.rb:354:in `new_constants_in’
C:/Dev/jruby/lib/ruby/gems/1.8/gems/activesupport-2.1.0/lib/active_support/dependencies.rb:214:in `load_file’
C:/Dev/jruby/lib/ruby/gems/1.8/gems/activesupport-2.1.0/lib/active_support/dependencies.rb:95:in `require_or_load’
C:/Dev/jruby/lib/ruby/gems/1.8/gems/activesupport-2.1.0/lib/active_support/dependencies.rb:260:in `load_missing_constant’
C:/Dev/jruby/lib/ruby/gems/1.8/gems/activesupport-2.1.0/lib/active_support/dependencies.rb:467:in `const_missing’
C:/Dev/jruby/lib/ruby/gems/1.8/gems/activesupport-2.1.0/lib/active_support/dependencies.rb:479:in `const_missing’
C:/Dev/jruby/lib/ruby/gems/1.8/gems/activesupport-2.1.0/lib/active_support/inflector.rb:283:in `module_eval’
C:/Dev/jruby/lib/ruby/gems/1.8/gems/activesupport-2.1.0/lib/active_support/inflector.rb:283:in `constantize’
C:/Dev/jruby/lib/ruby/gems/1.8/gems/activesupport-2.1.0/lib/active_support/core_ext/string/inflections.rb:143:in `constantize’
C:/Dev/jruby/lib/ruby/gems/1.8/gems/actionpack-2.1.0/lib/action_controller/routing/route_set.rb:386:in `recognize’
C:/Dev/jruby/lib/ruby/gems/1.8/gems/actionpack-2.1.0/lib/action_controller/dispatcher.rb:148:in `handle_request’
C:/Dev/jruby/lib/ruby/gems/1.8/gems/actionpack-2.1.0/lib/action_controller/dispatcher.rb:107:in `dispatch’
C:/Dev/jruby/lib/ruby/gems/1.8/gems/actionpack-2.1.0/lib/action_controller/dispatcher.rb:104:in `dispatch’
C:/Dev/jruby/lib/ruby/gems/1.8/gems/actionpack-2.1.0/lib/action_controller/dispatcher.rb:120:in `dispatch_cgi’
C:/Dev/jruby/lib/ruby/gems/1.8/gems/actionpack-2.1.0/lib/action_controller/dispatcher.rb:35:in `dispatch’
C:/Dev/jruby/lib/ruby/gems/1.8/gems/rails-2.1.0/lib/webrick_server.rb:112:in `handle_dispatch’
C:/Dev/jruby/lib/ruby/gems/1.8/gems/rails-2.1.0/lib/webrick_server.rb:78:in `service’
c:/Dev/jruby/lib/ruby/1.8/webrick/httpserver.rb:104:in `service’
c:/Dev/jruby/lib/ruby/1.8/webrick/httpserver.rb:65:in `run’
c:/Dev/jruby/lib/ruby/1.8/webrick/server.rb:173:in `start_thread’
C:/Dev/jruby/lib/ruby/gems/1.8/gems/ruby-plsql-0.2.4/lib/ruby_plsql.rb:23
C:/Dev/jruby/lib/ruby/gems/1.8/gems/ruby-plsql-0.2.4/lib/ruby_plsql.rb:36:in `require’
c:/Dev/jruby/lib/ruby/site_ruby/1.8/rubygems/custom_require.rb:36:in `require’
C:/Dev/jruby/lib/ruby/gems/1.8/gems/activesupport-2.1.0/lib/active_support/dependencies.rb:509:in `require’
C:/Dev/jruby/lib/ruby/gems/1.8/gems/activesupport-2.1.0/lib/active_support/dependencies.rb:354:in `new_constants_in’
C:/Dev/jruby/lib/ruby/gems/1.8/gems/activesupport-2.1.0/lib/active_support/dependencies.rb:509:in `require’
app/controllers/locations_controller.rb:1
app/controllers/locations_controller.rb:215:in `load’
C:/Dev/jruby/lib/ruby/gems/1.8/gems/activesupport-2.1.0/lib/active_support/dependencies.rb:215:in `load_file’
C:/Dev/jruby/lib/ruby/gems/1.8/gems/activesupport-2.1.0/lib/active_support/dependencies.rb:354:in `new_constants_in’
C:/Dev/jruby/lib/ruby/gems/1.8/gems/activesupport-2.1.0/lib/active_support/dependencies.rb:214:in `load_file’
C:/Dev/jruby/lib/ruby/gems/1.8/gems/activesupport-2.1.0/lib/active_support/dependencies.rb:95:in `require_or_load’
C:/Dev/jruby/lib/ruby/gems/1.8/gems/activesupport-2.1.0/lib/active_support/dependencies.rb:260:in `load_missing_constant’
C:/Dev/jruby/lib/ruby/gems/1.8/gems/activesupport-2.1.0/lib/active_support/dependencies.rb:467:in `const_missing’
C:/Dev/jruby/lib/ruby/gems/1.8/gems/activesupport-2.1.0/lib/active_support/dependencies.rb:479:in `const_missing’
C:/Dev/jruby/lib/ruby/gems/1.8/gems/activesupport-2.1.0/lib/active_support/inflector.rb:283:in `module_eval’
C:/Dev/jruby/lib/ruby/gems/1.8/gems/activesupport-2.1.0/lib/active_support/inflector.rb:283:in `constantize’
C:/Dev/jruby/lib/ruby/gems/1.8/gems/activesupport-2.1.0/lib/active_support/core_ext/string/inflections.rb:143:in `constantize’
C:/Dev/jruby/lib/ruby/gems/1.8/gems/actionpack-2.1.0/lib/action_controller/routing/route_set.rb:386:in `recognize’
C:/Dev/jruby/lib/ruby/gems/1.8/gems/actionpack-2.1.0/lib/action_controller/dispatcher.rb:148:in `handle_request’
C:/Dev/jruby/lib/ruby/gems/1.8/gems/actionpack-2.1.0/lib/action_controller/dispatcher.rb:107:in `dispatch’
C:/Dev/jruby/lib/ruby/gems/1.8/gems/actionpack-2.1.0/lib/action_controller/dispatcher.rb:104:in `dispatch’
C:/Dev/jruby/lib/ruby/gems/1.8/gems/actionpack-2.1.0/lib/action_controller/dispatcher.rb:120:in `dispatch_cgi’
C:/Dev/jruby/lib/ruby/gems/1.8/gems/actionpack-2.1.0/lib/action_controller/dispatcher.rb:35:in `dispatch’
C:/Dev/jruby/lib/ruby/gems/1.8/gems/rails-2.1.0/lib/webrick_server.rb:112:in `handle_dispatch’
C:/Dev/jruby/lib/ruby/gems/1.8/gems/rails-2.1.0/lib/webrick_server.rb:78:in `service’
c:/Dev/jruby/lib/ruby/1.8/webrick/httpserver.rb:104:in `service’
c:/Dev/jruby/lib/ruby/1.8/webrick/httpserver.rb:65:in `run’
c:/Dev/jruby/lib/ruby/1.8/webrick/server.rb:173:in `start_thread’
:1:in `start’
This error occurred while loading the following files:
ruby_plsql
Thank you!
-James.
Comment by James Good — March 10, 2009 @ 3:32 am |
Sorry, forgot to mention that I am using JRuby 1.2.0RC1, not Ruby. So I have no OCI8 library to use…
-James.
Comment by James Good — March 10, 2009 @ 3:35 am |
If you use JRuby and Rails then you need to initialize connection in the following way:
plsql.connection = ActiveRecord::Base.connection.raw_connection.connection
In next release I am going to make it simpler to specify that you want to use ActiveRecord connection, I know that current way looks too complex :)
BTW I also recommend to put this initialization either in environment.rb or event better in some special initializer file in config/initializers directory. It is not a good practice to put such initialization code in one particular controller.
Comment by Raimonds Simanovskis — March 10, 2009 @ 10:37 pm |
Thank you raimonds. However, my problem is that error message “private method ’split’ called for nil:NilClass on line 36 of ruby_plsql.rb
It seems to be that the ENV["PATH"] is returning nil. I wonder why that is- my jruby/bin directory is in my path.
Any ideas? Could it be a windows specific problem?
-James
Comment by James Good — March 11, 2009 @ 11:10 pm |
Yes it seems that ENV["PATH"] is nil according to error message that you are receiving. It’s quite strange as it should return your Windows PATH environment variable value. Can you try to run jirb and see what ENV["PATH"] returns?
One workaround would be that before requiring ruby_plsql you put:
ENV["PATH"] ||= “”
which will initialize ENV["PATH"] to empty string if it is nil.
Also in this case please copy ojdbc14.jar (Oracle JDBC driver) in %JRUBY_HOME%/lib directory. ruby_plsql is trying at first to find ojdbc14.jar in PATH but if it cannot find it then it should be located in lib directory under JRuby installation.
Comment by Raimonds Simanovskis — March 12, 2009 @ 3:58 pm |
Thanks, Raimonds. I have it working quite nicely now for standard oracle datatype parameters. However, I have some procedures to call which return cursor types, and complex record types (oracle objects). I guess I am on my own for having to implement those types?
Btw, in windows, the path is accessed through ENV["Path"] (note mixed case).
Thanks for the great gem!
-James.
Comment by James Good — March 13, 2009 @ 7:23 pm |
Yes, it seems that Windows allows you to define this environment variable both as PATH and Path but when you access it programmatically you need to specify correct case for letters :( Will need to add additional check in ruby-plsql gem.
Cursor and complex record type implementation is not so easy to be done as these types cannot be easily mapped to Ruby objects. As I typically use ruby-plsql for PL/SQL API packages which have just numbers, dates and strings as parameters I do not have such urgent need for other data types so therefore most probably I will not do this in nearest future. But if you have any patch or idea how to do it I would be glad to accept it :)
Comment by Raimonds Simanovskis — March 14, 2009 @ 1:00 am |
My ruby version is 1.8.6, ruby-oci8 2.0.2 and ruby-plsql 0.3.1
I would like to connect with my database through ruby-plsql.
But when I try to execute my stored function I get an error ‘No PL/SQL connection’.
Everything is ok when I select data from oracle database directly through oci8:
irb(main):001:0> require ‘oci8′
=> true
irb(main):002:0> require ‘ruby_plsql’
=> true
irb(main):003:0> conn=OCI8.new(‘myusername’,'mypaswd’,'mydatabase’)
=> #<OCI8:0xded9e98 @privilege=nil, @prefetch_rows=nil, @ctx=[0, #, nil, 65535], @svc=#>
irb(main):004:0> conn.exec(’select description1 from gi_blacklists’) do |r|
irb(main):005:1* puts r
irb(main):006:1> end
WPIS NA PODST ROSZCZENIA 14341.
WPIS NA PODST ROSZCZENIA 19482.
WPIS NA PODST ROSZCZENIA 9521.
WPIS NA PODST ROSZCZENIA 6189.
WPIS NA PODST ROSZCZENIA 1783.
WPIS NA PODST ROSZCZENIA 3141.
WPIS NA PODST ROSZCZENIA 21395.
=> 7
I’ve created a stored function big_letter:
CREATE OR REPLACE
FUNCTION gi.big_letter (p_str IN VARCHAR2)
RETURN VARCHAR2
IS
BEGIN
RETURN UPPER (p_str);
END;
/
Now, I try to call this function in ruby:
irb(main):007:0> puts plsql.big_letter(‘ala ma kota’)
ArgumentError: No PL/SQL connection
from C:/Ruby/lib/ruby/gems/1.8/gems/ruby-plsql-0.3.1/lib/plsql/schema.rb:112:in `method_missing’
from (irb):7
from :0
Am I doing anything wrong?
Thanks in advance
Comment by Alicja — August 25, 2009 @ 4:02 pm |
You need to specify for ruby-plsql which connection to use, in your example include
plsql.connection = conn
before trying to call plsql.big_letter
Comment by Raimonds Simanovskis — August 25, 2009 @ 5:31 pm |
Thanks! It works really nice:)
Undoubtedly I will use your cool gem.
Alicja
Comment by Alicja — August 26, 2009 @ 9:25 am |
Raimonds,
Can this gem execute a client side .sql file from ruby and return the stdout to a ruby variable? Much like I might use sqlplus?
Thanks for your help.
Cris
Comment by Cris — February 8, 2010 @ 8:12 pm |