Some time ago I created ruby-plsql gem which provides simple Ruby API for Oracle PL/SQL stored procedures.
Initially this gem supported just MRI with ruby-oci8 library which provides connectivity to Oracle database as this was my main development and production environment for Ruby & Oracle applications. But as JRuby is fast growing alternative Ruby deployment platform and as it can be integrated into Oracle Fusion middleware platform (e.g. Oracle Mix is running on JRuby on Oracle Fusion middleware) then I planned to support JRuby as well.
I started to work on JRuby support during RailsConf JRuby hackfest and initially this did not seem very hard task for me. But as I did not know JDBC very well it took me much more time than initially planned. And unfortunately JDBC is also much less powerful compared to ruby-oci8 library for construction of dynamic PL/SQL calls. In addition I needed to strugle with Ruby and JDBC data type mappings which differs from Ruby and ruby-oci8 data type mappings.
But finally I have completed JRuby support and released ruby-plsql gem version 0.2.0. And good news are that from usage perspective ruby-plsql behaves identically on MRI and JRuby – at least my RSpec tests are telling so.
To install this gem on JRuby execute
sudo jruby -S gem install ruby-plsql
or
sudo jgem install ruby-plsql
depending on how you have installed JRuby on your computer.
Source code of ruby-plsql is located on GitHub where you can find usage examples in RSpec tests.
It seams that you also need to copy ojdbc14.jar to $JRUBY_HOME/lib directory as otherwise jruby cannot load this jar file and ruby-plsql will fail with “No suitable driver” exception.
Comment by Raimonds Simanovskis — June 26, 2008 @ 9:55 pm |
Great job! yep, ojdbc14.jar is actually needed!
Comment by Jesse Hu — June 27, 2008 @ 3:51 am |
Cool stuff, and it’s great to see other gem developers considering JRuby support. Feel free to announce on JRuby user list if you like.
Comment by Charles Oliver Nutter — June 27, 2008 @ 4:30 am |
Ray,
Thanks for the great work! I cannot seem to find the syntax for setting up a connection with jruby. The docs only cover setting up a new OCI8 based connection. If it is documented, would you mind pointing me in the right direction?
Comment by Brett — July 31, 2008 @ 3:27 pm |
You just need to assign JDBC connection object to plsql.connection.
So if you would like to use ruby-plsql without Rails / ActiveRecord then you need to establish connection, for example, in the following way:
import java.sql.Statement
import java.sql.Connection
import java.sql.SQLException
import java.sql.Types
import java.sql.DriverManager
DriverManager.registerDriver Java::oracle.jdbc.driver.OracleDriver.new
jdbc_conn = DriverManager.getConnection(“jdbc:oracle:thin:@server:1521:SID”,”user”,”password”)
plsql.connection = jdbc_conn
if you are using it with Rails / ActiveRecord JDBC adapter then you can just put in environment.rb (or some initialization file):
plsql.connection = ActiveRecord::Base.connection.connection
Comment by Raimonds Simanovskis — August 3, 2008 @ 7:43 pm |
I’m having problems with it.
That is what I did:
- Included ojdbc14.jar in $JRUBY_HOME/lib;
- Added $JRUBY_HOME/lib to PATH windows enviroment variable;
And when I try to execute, it returns the following error:
C:\project\trunk>jruby -S script\console
Loading development environment (Rails 2.1.0)
>> require “ruby_plsql”
To use ruby_plsql you must have Oracle JDBC driver installed.
=> ["RubyPlsql"]
>> plsql.connection = ActiveRecord::Base.connection.raw_connection
ArgumentError: Unknown raw driver
from C:/Documents and Settings/murad/Desktop/jruby-1.1.3/lib/ruby/gems/1.8/gems/ruby-plsql-0.2.2/lib/plsql/connection.rb:17:in `create’
from C:/Documents and Settings/murad/Desktop/jruby-1.1.3/lib/ruby/gems/1.8/gems/ruby-plsql-0.2.2/lib/plsql/schema.rb:28:in `connection=’
from (irb):3
If I try this other way
import java.sql.Statement
import java.sql.Connection
import java.sql.SQLException
import java.sql.Types
import java.sql.DriverManager
DriverManager.registerDriver Java::oracle.jdbc.driver.OracleDriver.new
jdbc_conn = DriverManager.getConnection(“jdbc:oracle:thin:@server:port:simu3″,”login”,”pass”)
require “ruby_plsql”
plsql.connection = jdbc_conn
The connection works OK but when I try
plsql.bra_customhr_eval_pk.validate_login_p(:p_username => “MMENEZES”, :p_password => “@senhateste2006″, :p_validate => nil)[:p_validate]
I get the following error
NoMethodError: undefined method `setStringAtName’ for #
from C:/Documents and Settings/murad/Desktop/jruby-1.1.3/lib/ruby/gems/1.8/gems/ruby-plsql-0.2.2/lib/plsql/connection.rb:377:in `set_bind_variable’
from C:/Documents and Settings/murad/Desktop/jruby-1.1.3/lib/ruby/gems/1.8/gems/ruby-plsql-0.2.2/lib/plsql/connection.rb:297:in `bind_param’
from C:/Documents and Settings/murad/Desktop/jruby-1.1.3/lib/ruby/gems/1.8/gems/ruby-plsql-0.2.2/lib/plsql/procedure.rb:157:in `exec’
from C:/Documents and Settings/murad/Desktop/jruby-1.1.3/lib/ruby/gems/1.8/gems/ruby-plsql-0.2.2/lib/plsql/procedure.rb:155:in `each’
from C:/Documents and Settings/murad/Desktop/jruby-1.1.3/lib/ruby/gems/1.8/gems/ruby-plsql-0.2.2/lib/plsql/procedure.rb:155:in `exec’
from C:/Documents and Settings/murad/Desktop/jruby-1.1.3/lib/ruby/gems/1.8/gems/ruby-plsql-0.2.2/lib/plsql/package.rb:31:in `method_missing’
from (irb):19
Any help will be appreciate!
Thanks,
Marcelo Murad
Comment by Marcelo Murad — September 17, 2008 @ 8:44 pm |
I found out that the correct way how to assign JDBC connection from Rails JDBC adapter is:
plsql.connection = ActiveRecord::Base.connection.raw_connection.connection
And I also understood why you get this “you must have Oracle JDBC driver installed” error. I use the following code to find ojdbc14.jar in PATH:
ojdbc_jar = “ojdbc14.jar”
ojdbc_jar_path = ENV["PATH"].split(“:”).find{|d| File.exists?(File.join(d,ojdbc_jar))}
but on Windows PATH directories are separated by “;” and not by “:” as in Unix. Sorry that I did not think about Windows collegues :)
But in Rails case probably you can ignore this message as ActiveRecord JDBC adapter will already load all necessary JDBC drivers.
Comment by Raimonds Simanovskis — September 17, 2008 @ 10:22 pm |
Thanks Raimonds,
I did a fast update the file now connection association works, but I still get the setStringAtName error.
>> require “ruby_plsql”
=> ["Statement", "Connection", "RubyPlsql", "Types", "DriverManager", "SQLException"]
>> plsql.connection = ActiveRecord::Base.connection.raw_connection.connection
=> #
>> plsql.bra_customhr_eval_pk.validate_login_p(:p_username => “MMENEZES”, :p_password => “@senhateste2006″, :p_validate => nil)[:p_validate]
NoMethodError: undefined method `setStringAtName’ for #
from C:/Documents and Settings/murad/Desktop/jruby-1.1.3/lib/ruby/gems/1.8/gems/ruby-plsql-0.2.2/lib/plsql/connection.rb:377:in `set
_bind_variable’
from C:/Documents and Settings/murad/Desktop/jruby-1.1.3/lib/ruby/gems/1.8/gems/ruby-plsql-0.2.2/lib/plsql/connection.rb:297:in `bin
d_param’
from C:/Documents and Settings/murad/Desktop/jruby-1.1.3/lib/ruby/gems/1.8/gems/ruby-plsql-0.2.2/lib/plsql/procedure.rb:157:in `exec
‘
from C:/Documents and Settings/murad/Desktop/jruby-1.1.3/lib/ruby/gems/1.8/gems/ruby-plsql-0.2.2/lib/plsql/procedure.rb:155:in `each
‘
from C:/Documents and Settings/murad/Desktop/jruby-1.1.3/lib/ruby/gems/1.8/gems/ruby-plsql-0.2.2/lib/plsql/procedure.rb:155:in `exec
‘
from C:/Documents and Settings/murad/Desktop/jruby-1.1.3/lib/ruby/gems/1.8/gems/ruby-plsql-0.2.2/lib/plsql/package.rb:34:in `method_
missing’
from (irb):4
Comment by Marcelo Murad — September 17, 2008 @ 10:47 pm |
It was a older version of ojdbc14.jar. I uypdate it and it works like a charm!
Comment by Marcelo Murad — September 17, 2008 @ 11:27 pm |
Raimonds,
Try as I might, I just cannot get this to work. My current gems are:
actionmailer (2.1.2, 2.0.2)
actionpack (2.1.2, 2.0.2)
activerecord (2.1.2, 2.0.2)
activerecord-jdbc-adapter (0.8.2)
activeresource (2.1.2, 2.0.2)
activesupport (2.1.2, 2.0.2)
composite_primary_keys (1.1.0, 0.8.6)
jruby-openssl (0.3)
rails (2.1.2, 2.0.2)
rake (0.8.3)
rspec (1.1.11)
ruby-plsql (0.2.3)
sources (0.0.1)
And the code I am using is:
require “rubygems”
gem “activerecord”
gem “ruby-plsql”
require ‘active_record’
require ‘ruby_plsql’
require ‘pp’
ActiveRecord::Base.establish_connection(:adapter => “jdbc”,
:driver => “oracle.jdbc.OracleDriver”,
:url => “removed”,
:username => “removed”,
:password => “removed”)
class Foo < ActiveRecord::Base
plsql = ActiveRecord::Base.connection.raw_connection
puts plsql.class.to_s
PLSQL::Procedure.find(plsql,’bal.test_it’)
res = plsql.test_it(‘foo’)
pp res
end
I keep getting an error of:
jruby-1.1.5/lib/ruby/gems/1.8/gems/ruby-plsql-0.2.3/lib/plsql/procedure.rb:5:in `find’: undefined method `schema_name’ for # (NoMethodError)
I have been through a lot of the source code, and cannot seem to track down where these extra methods such as ’schema_name’ are getting added to the JdbcConnection class – have you any ideas as to where I am going wrong?
Any help is much appreciated!
Stephen.
Comment by Stephen — November 13, 2008 @ 5:00 pm |
@Stephen
Instead of
plsql = ActiveRecord::Base.connection.raw_connection
you need to write (in JRuby & JDBC case):
plsql.connection = ActiveRecord::Base.connection.raw_connection.connection
And better please put this line outside class definition.
And you should not use PLSQL::Procedure.find :) Just use plsl.package_name.procedure_name
Comment by Raimonds Simanovskis — November 13, 2008 @ 7:36 pm |
Raimonds,
Works perfectly now for my test code at least. Now to port my OCI8 MRI code to ruby-plsql jruby instead …
Thanks!
Comment by Stephen — November 14, 2008 @ 12:27 pm |
There is problem with synonyms. If plsql pacakage is available as synonym only then plsql gem is ignorig this because it selects only objects with type PACKAGE / PROCEDURE / FUNCTION. For schema syonyms it is needed to add objects with type SYNONYM, too. This is needed for class Package and probably Procedure, too.
Comment by Janis — February 11, 2009 @ 2:55 pm |
Hi Raimonds,
ruby-plsql is running fine for most of my work.
today though I encountered following issue.
I have a function in package STAGING
FUNCTION get_created_ddl (object_name in varchar2, schema_name in varchar2 default null)
RETURN t_created_ddl;
where t_created_ddl is a record with 2 date fields.
TYPE t_created_ddl IS RECORD (created_date date, ddl_date date);
in PL/SQL I can execute it in following way:
procedure test_get_created_ddl (object_name in varchar2, schema_name in varchar2 default null) is
result t_created_ddl;
begin
result := STAGING.get_created_ddl(object_name, schema_name);
dbms_output.put_line(object_name || ‘: created: ‘ || result.created_date || ‘ ddl: ‘ || result.ddl_date);
end;
when trying to call the function get_created_ddl() in plsql I get following:
require ‘ruby_plsql’
plsql.connection = OCI8.new(“username”,”pass”,”sid”)
p plsql.staging.get_created_ddl(“lb_individuals_specs_eu_vw”,”lm_emea”)
produces:
d:/ruby186/lib/ruby/gems/1.8/gems/ruby-plsql-0.2.3/lib/plsql/oci_connection.rb:1
11:in `ruby_value_to_ora_value’: undefined method `to_datetime’ for “lb_individu
als_specs_eu_vw”:String (NoMethodError)
from d:/ruby186/lib/ruby/gems/1.8/gems/ruby-plsql-0.2.3/lib/plsql/proced
ure.rb:201:in `ruby_value_to_ora_value’
from d:/ruby186/lib/ruby/gems/1.8/gems/ruby-plsql-0.2.3/lib/plsql/proced
ure.rb:160:in `exec’
from d:/ruby186/lib/ruby/gems/1.8/gems/ruby-plsql-0.2.3/lib/plsql/proced
ure.rb:158:in `each’
from d:/ruby186/lib/ruby/gems/1.8/gems/ruby-plsql-0.2.3/lib/plsql/proced
ure.rb:158:in `exec’
from d:/ruby186/lib/ruby/gems/1.8/gems/ruby-plsql-0.2.3/lib/plsql/packag
e.rb:34:in `method_missing’
from pls.rb:7
shell returned 1
Is it a limitation of ruby-plsql that it does not return custom types (like a record in this example)?
many thanks,
chris
Comment by chris — February 17, 2009 @ 2:06 pm |
Yes, ruby-plsql currently does not support custom types as I need to map each Oracle type to Ruby type.
Currently I support just NUMBER, VARCHAR2, DATE, CLOB data types.
Comment by Raimonds Simanovskis — February 17, 2009 @ 4:01 pm |
Hi Ray
I got a problem in executing the specific store procedure in oracle via JDBC. But it is no problem to exec other store procedure, and all store procedures are working fine if I use SQLPLUS to connect to the database..
Please check the below link for detail.
http://www.ruby-forum.com/topic/192304
I am using JRuby 1.3.1, ruby-plsql-0.3.1
Please give me some advices
Many thanks
Valentino
Comment by Valentino — July 29, 2009 @ 4:25 am |
Why are you doing it in so complex way? Have you tried
plsql.proc_cuid_insert_cuid_detail(‘BBC338′, ”, ‘P5954804′, ‘TESTING, TEST ZOOO’,
‘LIS’, ‘QMH’, nil, nil)
This is the main idea of ruby-plsql gem that it provides nice Ruby syntax for PL/SQL procedure calls and you don’t need to do this huge parameter binding. Alternative syntax is to use named parameter hash which would be preferred way if you have so many parameters.
If you get the same error then it would be necessary to identify in which exact place in your procedure this exception is raised – based on error message it complains that bind variable type is not matching table column type.
Comment by Raimonds Simanovskis — July 29, 2009 @ 2:44 pm |
Hi Ray
I tried your suggested method.
result = plsql.proc_cuid_insert_cuid_detail(‘BBC338′,”,’P5954804′,’TESTING, JA ZOOO’,'LIS’,'QMH’,nil,nil)
Same error occor.
puts result[:raiserror_text].strip
=> “Error in cuid_detail table insertion!ORA-01461: can bind a LONG value only for insert into a LONG column”
I got no problem to execute the same store procedure with same parameters via SQLPLUS.
I think there is a part in somewhere to change the datatype to LONG…but actually my table schema do not have LONG column.. I tried to test with oracle jdbc adapter and your oracle_enhanced adapter. The result is the same…
I have really no idea, and your expertise advice is valuable to me.
Many thanks
Valentino
Comment by Valentino — July 30, 2009 @ 4:33 am |
Hi Raimonds,
I noticed a little different ruby_plsql behaviour to what sqlplus does in the following example:
I have a plssql function in package chrispack called prepare_phone
in sqlplus
SQL> select chrispack.prepare_phone(82,’+49 (89) 1234,123′) from dual;
CHRISPACK.PREPARE_PHONE(82,’+49(89)1234,123′)
——————————————————————————–
0891234
when I run the same with in ruby:
p plsql.chrispack.prepare_phone(82,”+49 (89) 1234,123″);
it returns:
“8912345″ (without leading 0)
Is it ruby_plsql behaviour or some ruby issue?
thanks,
chris
Comment by chris — August 5, 2009 @ 1:51 pm |
update to this:
my prepare_phone definition looks like this
FUNCTION prepare_phone (country_id in number, work_phone_no in varchar, leading_zero in number default 1) RETURN varchar
I just discovered in ruby_plsql the function returns expected result when the third parameter is provided:
p plsql.chrispack.prepare_phone(82,”+49 (89) 1234,123″,1);
“0891234″
p plsql.chrispack.prepare_phone(82,”+49 (89) 1234,123″); — should deliver same result but does not
“891234″
is it because of the default value in third parameter not being respected by ruby_plsql?
thanks,
chris
Comment by chris — August 5, 2009 @ 2:17 pm |
I found an issue – if using sequential arguments and if not all arguments are specified then I add missing arguments with value NULL. Probably in some projects had APIs with lot of mandatory parameters and therefore implemented it :) As probably some our applications depend on that it wouldn’t be very good to turn this “feature” off (probably in next version I could add some configuration option which could control this behavior).
One workaround is to use named parameters when calling it from Ruby:
plsql.prepare_phone(:country_id => 82, :work_phone_no => ‘+49 (89) 1234,123′)
In this case no additional parameters would be passed.
Other option would be always to replace NULL values in parameters with default values at PL/SQL side :)
Comment by Raimonds Simanovskis — August 6, 2009 @ 10:10 pm |
Hi,
I have a stored procedure named as “display”. In this stored procedure I have dbms_out.put_line statment. I am not able to get the statement specified in the dbms_output in the standard output of my terminal.
In my stored procedure I have a statement “dbms_output.enable” before the put_line statement. Eventhough I am not able to get the standard output to my terminal.
Please guide me on this.
Thanks,
Vikas Rana
Comment by Vikas — September 1, 2009 @ 2:12 pm |
Currently it is not possible to get dbms_output to standard output of Ruby process.
Maybe in later releases will add such functionality (I have already one patch submitted which does similar thing for oracle_enhanced adapter).
Comment by Raimonds Simanovskis — September 1, 2009 @ 10:14 pm |
Hi Rai,
That means currently there is no way to get dbms_output to standard output when invoking stored procedure using pl-sql gem. Ok :( ..
Thanks Rai for your valuable comments.
Thanks,
Vikas Rana
Comment by Vikas — September 2, 2009 @ 6:39 am |