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 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.
- 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.
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:
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.
sqlplus doesn’t commit by default; you need to type
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
Sometimes, despite your best intentions, you simply have to use an Oracle database from Ruby. Hopefully this post makes it a little bit easier.