ruby-plsql

Ruby API for calling Oracle PL/SQL procedures.

DESCRIPTION

ruby-plsql gem provides simple Ruby API for calling Oracle PL/SQL procedures. It could be used both for accessing Oracle PL/SQL API procedures in legacy applications as well as it could be used to create PL/SQL unit tests using Ruby testing libraries.

NUMBER, BINARY_INTEGER, PLS_INTEGER, NATURAL, NATURALN, POSITIVE, POSITIVEN, SIGNTYPE, SIMPLE_INTEGER, VARCHAR, VARCHAR2, NVARCHAR2, CHAR, NCHAR, DATE, TIMESTAMP, CLOB, BLOB, BOOLEAN, PL/SQL RECORD, TABLE, VARRAY, OBJECT and CURSOR types are supported for input and output parameters and return values of PL/SQL procedures and functions.

ruby-plsql is tested on CI against Ruby 2.4, 2.5, 2.7, 3.1, 3.2, 3.3, 3.4, 4.0 and JRuby 10.1.1.0. Ruby 2.4–3.1 remain in CI to verify compatibility with older ActiveRecord releases (see gemfiles/). The CI runs the suite against Oracle Database 23ai Free and Oracle Database XE 11g.

USAGE

Calling PL/SQL functions and procedures:

require"rubygems"require"ruby-plsql"plsql.connection=OCI8.new("hr","hr","xe")plsql.test_uppercase('xxx')# => "XXX"plsql.test_uppercase(:p_string=>'xxx')# => "XXX"plsql.test_copy("abc",nil,nil)# => { :p_to => "abc", :p_to_double => "abcabc" }plsql.test_copy(:p_from=>"abc",:p_to=>nil,:p_to_double=>nil)# => { :p_to => "abc", :p_to_double => "abcabc" }plsql.hr.test_uppercase('xxx')# => "XXX"plsql.test_package.test_uppercase('xxx')# => 'XXX'# PL/SQL records or object type parameters should be passed as Hashp_employee={:employee_id=>1,:first_name=>'First',:last_name=>'Last',:hire_date=>Time.local(2000,01,31)}plsql.test_full_name(p_employee)# TABLE or VARRAY parameters should be passed as Arrayplsql.test_sum([1,2,3,4])# Nested objects or arrays are also supportedp_employee={:employee_id=>1,:first_name=>'First',:last_name=>'Last',:hire_date=>Time.local(2000,01,31),:address=>{:street=>'Street',:city=>'City',:country=>'Country'},:phones=>[{:type=>'mobile',:phone_number=>'123456'},{:type=>'fixed',:phone_number=>'654321'}]}plsql.test_store_employee(p_employee)# Returned cursor can be fetchedplsql.test_cursordo |cursor|
cursor.fetch# => one row from cursorcursor.fetch_all# => all rows from cursorendplsql.connection.autocommit=falseplsql.commitplsql.rollbackplsql.logoff

Look at RSpec tests under spec directory for more usage examples.

Note: named arguments in procedures calls should be in lower case.

Table operations:

ruby-plsql also provides simple API for select/insert/update/delete table operations (with Sequel-like syntax). This could be useful if ruby-plsql is used without ActiveRecord (e.g. for writing PL/SQL unit tests):

# insert record in tableemployee={:employee_id=>1,:first_name=>'First',:last_name=>'Last',:hire_date=>Time.local(2000,01,31)}plsql.employees.insertemployee# INSERT INTO employees VALUES (1, 'First', 'Last', ...)# insert many recordsemployees=[employee1,employee2, ... ]# array of many Hashesplsql.employees.insertemployees# insert many records as list of valuesplsql.employees.insert_values[:employee_id,:first_name,:last_name],[1,'First 1','Last 1'],[2,'First 2','Last 2']# select one recordplsql.employees.first# SELECT * FROM employees# fetch first row => {:employee_id => ..., :first_name => '...', ...}plsql.employees.first(:employee_id=>1)# SELECT * FROM employees WHERE employee_id = 1plsql.employees.first("WHERE employee_id = 1")plsql.employees.first("WHERE employee_id = :employee_id",1)# select many recordsplsql.employees.all# => [{...}, {...}, ...]plsql.employees.all(:order_by=>:employee_id)plsql.employees.all("WHERE employee_id > :employee_id",5)# count recordsplsql.employees.count# SELECT COUNT(*) FROM employeesplsql.employees.count("WHERE employee_id > :employee_id",5)# update recordsplsql.employees.update(:first_name=>'Second',:where=>{:employee_id=>1})# UPDATE employees SET first_name = 'Second' WHERE employee_id = 1# delete recordsplsql.employees.delete(:employee_id=>1)# DELETE FROM employees WHERE employee_id = 1# select from sequencesplsql.employees_seq.nextval# SELECT employees_seq.NEXTVAL FROM dualplsql.employees_seq.currval# SELECT employees_seq.CURRVAL FROM dual

Usage with Rails:

If using with Rails then include in initializer file:

plsql.activerecord_class=ActiveRecord::Base

and then you do not need to specify plsql.connection (this is also safer when ActiveRecord reestablishes connection to database).

Connection options: :database, :service_name, :sid

connect! accepts three mutually-exclusive ways to identify the target Oracle instance, matching the oracle-enhanced adapter:

# By service name (recommended for 12c+ / PDBs)plsql.connect!username: "hr",password: "hr",host: "localhost",service_name: "MYSERVICENAME"# By SID (legacy single-instance, e.g. Oracle 11g XE)plsql.connect!username: "hr",password: "hr",host: "localhost",sid: "MYSID"# `:database` is still accepted and is treated as a service nameplsql.connect!username: "hr",password: "hr",host: "localhost",database: "MYSERVICENAME"

Supplying more than one of :database, :service_name, :sid raises ArgumentError. Both :service_name and :sid work under the OCI driver (MRI) and the JDBC driver (JRuby). Under JRuby:

• :service_name builds jdbc:oracle:thin:@//host:port/service_name
• :sid builds jdbc:oracle:thin:@host:port:SID

The legacy database: ":MYSID" colon-prefix overload still works for one release but is deprecated; use sid: "MYSID" instead.

Cheat Sheet:

You may have a look at this Cheat Sheet for instructions on how to use ruby-plsql

INSTALLATION

Install as gem with

gem install ruby-plsql

or include gem in Gemfile if using bundler.

In addition install either ruby-oci8 (for MRI/YARV) or copy Oracle JDBC driver to $JRUBY_HOME/lib (for JRuby).

If you are using MRI Ruby implementation then you need to install ruby-oci8 gem (version 2.1 or higher) as well as Oracle client, e.g. Oracle Instant Client.

If you are using JRuby then you need to download the appropriate Oracle JDBC driver for your Java version. CI exercises ojdbc17.jar and ojdbc11.jar on Java 21.

And copy this file to one of these locations. JDBC driver will be searched in this order:

• in JRUBY_HOME/lib directory
• in ./lib directory of Rails application
• or include path to JDBC driver jar file in Java CLASSPATH
• in some directory which is in PATH

If you put multiple versions of JDBC driver in the same directory the higher version one will be used.

Make sure to setup the following Oracle-specific environment variables properly

• NLS_LANG - preferred value NLS_LANG=AMERICAN_AMERICA.AL32UTF8
• ORA_SDTZ The setting should point a machine timezone like: ORA_SDTZ=Europe/Riga, otherwise Oracle by default uses a Fixed-offset timezone (like 03:00) that is not daylight saving (DST) aware, which will lead to wrong translations of the timestamp values between Ruby code (DTS-aware) and Oracle session (non-DST-aware).
• ORACLE_HOME

You may either alter your environment settings or set the values in file spec/support/custom_config.rb. Sample file custom_config.rb.sample shows how to do that.

Make sure you use correct version of Oracle client for database you're connecting to. Otherwise you may encounter TimeZone errors like this

TESTS

See CONTRIBUTING.md for how to set up a development environment and run the spec suite.

LINKS

• Source code: http://github.com/rsim/ruby-plsql
• Bug reports / Feature requests: http://github.com/rsim/ruby-plsql/issues
• Discuss at oracle_enhanced adapter group: http://groups.google.com/group/oracle-enhanced

CONTRIBUTORS

Thanks to everyone who has contributed to ruby-plsql over the years. The up-to-date list is maintained on GitHub: https://github.com/rsim/ruby-plsql/graphs/contributors

LICENSE

(The MIT License)

Copyright (c) 2008-2014 Raimonds Simanovskis

Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the 'Software'), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED 'AS IS', WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.