Interfacing Legacy Oracle PL/SQL and Modern Ruby Code

My last couple of blog posts have been about the backend and middleware work I did at my previous employer. I wrote about two Ruby gems, ClassyHash and HashFormer, that help with data validation and transformation in backend APIs and in middleware. Today we’ll look briefly at some code snippets to help Ruby developers interface with older systems that were written in Oracle PL/SQL, possibly using Oracle applications like EBS (E-Business Suite).

To help launch my previous employer’s updated site, I kind of dove head-first into the deep end of PL/SQL, teaching myself the PL/SQL language, refactoring thousands of lines of legacy code that was not designed with maintenance in mind, and learning chunks of Oracle EBS (both the arcane UI and the even more arcane internal PL/SQL interfaces) over a period of several months. Let my pain be your gain. Continue reading for essential gems, some tips and tricks, and a wrapper script to make the sqlplus command-line interface a little nicer for Ruby developers.

Ruby and PL/SQL

If you’re migrating from an old codebase that already interfaced with your Oracle systems, you might have a collection of existing optimized SQL queries you want to bring over. If not, you probably want to use a Ruby-like interface and an ORM, rather than having to write raw SQL. You might also have PL/SQL stored procedures that you need to call from your Ruby code. Fortunately, there’s a pretty good set of Ruby gems for all of these use cases.

There are three major gems you’ll want to use when working with an Oracle database: ruby-oci8, ruby-plsql, and Sequel.

ruby-oci8 provides a direct interface to cursors, queries, etc. Results can be returned as a Ruby Hash, but it’s otherwise pretty raw. This gem is useful if you have existing SQL queries you want to use that, for whatever reason, don’t work well enough or fast enough with the Sequel gem. The other Ruby gems use this gem to talk to Oracle databases.
ruby-plsql is a truly amazing gem that lets you work with PL/SQL packages, procedures, and objects directly in Ruby as if they were Ruby objects, as easy as plsql.package_name.procedure_name(arg_name: 'value'). This gem is indispensable for working with a legacy PL/SQL codebase from Ruby.
Sequel is a popular ORM for Ruby, and its basic query interface might be easier for you remember how to use than ruby-plsql’s.

Both ruby-plsql and Sequel have their own ways they want to talk to ruby-oci8, so using both in the same codebase can be kind of tricky. Additionally, Sequel doesn’t appear to be designed with access to the raw ruby-oci8 connection in mind, so accessing all three over the same connection (essential for mixing gems in a single DB transaction) is a bit tricky.

You might run into bugs in the gems, especially if your application is multi-threaded. For the most part the ruby-oci8 gem author(s) are quite skilled and very responsive to issues, like a crash (and one proposed fix) I encountered when mixing threads, ruby-oci8, and ruby-plsql. Note that this has been fixed.

Anyway, here’s the interface code. It relies on Sequel yielding the ruby-oci8 connection object when running a transaction. If you’re not using the Sequel gem, you can take a slightly simpler approach with just ruby-oci8 and ruby-plsql by opening a connection to ruby-oci8 and passing it to ruby-plsql.

#!/usr/bin/env ruby # Simplified demo showing the use of the Sequel gem, raw OCI8 connection, and # ruby_plsql gem on a single connection. Uses undocumented yield behavior from # Sequel, so might not work with different Sequel versions. # by Mike Bourgeous # These must be set before requiring oci8 ENV['NLS_LANG'] = 'american_america.utf8' ENV['TNS_ADMIN'] = File.expand_path(File.dirname(__FILE__)) require 'oci8' require 'sequel' require 'ruby_plsql' require 'yaml' require 'awesome_print' # Yields oci8, plsql, sequel in a transaction that auto-commits. In a real app # this might need some kind of thread management. def db_do DB.transaction do |oci8| p = plsql(:simple_plsql_demo) p.connection = oci8 yield oci8, p, DB end end # Returns database output from DBMS_OUTPUT (call dbms_output.enable first). def get_output lines = [] db_do do |oci8, plsql, sequel| while (line = plsql.dbms_output.get_line)[:status] == 0 lines << line[:line] end end lines end AwesomePrint.defaults = { indent: -2 } # Connect using Sequel config = YAML.load_file('oracle.yml') ap(config: config) DB = Sequel.connect(config) # Query using Sequel # http://sequel.jeremyevans.net/documentation.html db_do do |oci8, plsql, sequel| ap sequel: sequel[:dba_objects].where(object_type: 'TABLE').exclude(owner: 'SYS').first end # Query using OCI8 # https://github.com/kubo/ruby-oci8 db_do do |oci8, plsql, sequel| c = oci8.exec('SELECT CAST(1 + 1 AS INTEGER) AS two FROM DUAL') while row = c.fetch_hash do ap oci8: row end c.close end # Query using ruby-plsql # https://github.com/rsim/ruby-plsql db_do do |oci8, plsql, sequel| ap plsql: plsql.dba_objects.first(object_type: 'TABLE') end # Run code in PL/SQL packages using ruby-plsql db_do do |oci8, plsql, sequel| plsql.dbms_output.enable plsql.dbms_output.put_line('Database output will be buffered and can be retrieved') plsql.dbms_output.put_line('See the get_output method above') ap plsql_output: get_output end

Command line

As a Ruby developer who’s been asked to interface with an Oracle database, you’ll probably also want a nice command line interface for accessing the database. At least at the version we were using at my previous employer, the sqlplus tool was lacking some important features like terminal size detection and line editing. Coming from MySQL/MariaDB and PostgreSQL, sqlplus will feel quite Spartan. Here are the scripts I used to launch sqlplus (and other Oracle tools using the same environment settings) with line editing, a colorful prompt, and some reasonable defaults for query editing and page size:

#!/bin/sh # Launches an application with the Oracle instant client environment set. # Created by Mike Bourgeous, DeseretBook.com export CLIENT_PATH=/usr/lib/oracle/11.2/client64/lib export PATH="$PATH:$CLIENT_PATH" export LD_LIBRARY_PATH="$CLIENT_PATH" export SQLPATH="$HOME/.oracle:$CLIENT_PATH" export TNS_ADMIN="$HOME/.oracle" export NLS_LANG="american_america.utf8" "$@"

Some quick tips

The legacy PL/SQL code I encountered was formatted very differently from typical Ruby code, using an awful lot of vertical screen space. Sometimes you’ll find procedure calls with dozens of parameters. I strongly suggest using a second monitor in portrait orientation to fit as much as possible on one screen.

Another note: it can take a bit of practice to know which sites and search queries will give reliable information on Oracle. Some Oracle-related queries are dominated by SEO’d malware-serving sites, so make sure your browser is up to date and be careful out there. I also recommend finding Oracle’s official PDF and HTML documentation whenever it’s available. Despite there being tens of thousands of pages of it, you should still put the time in to learning how to skim it for what you need.

Remember that sqlplus doesn’t commit by default; you need to type COMMIT or ROLLBACK when you’re done modifying data, or you might block other users of the database. The v$Lock view can be useful for debugging this type of issue.

Finally, learn the introspective tools available to you to explore your database, such as the dba_objects table and desc command.

Conclusion

Sometimes, despite your best intentions, you simply have to use an Oracle database from Ruby. Hopefully this post makes it a little bit easier.

blog.MikeBourgeous

Making life more livable through technology