Using the OCI8, Sequel, and ruby_plsql gems all together

Basic_ruby_oracle_demo.rb

#!/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

Gemfile

source 'https://rubygems.org'

gem 'sequel', '~> 4.36.0'
gem 'ruby-oci8'
gem 'ruby-plsql'
gem 'awesome_print'

oracle.yml

:adapter: oracle
:username: [user]
:password: [pass]
:database: TEST

tnsnames.ora

# Copy or modify the TEST= definition below, changing IP_Address_Here,
# Port_Number_Here, "TEST =" and the SERVICE_NAME.  Multiple definitions may be
# present in one file.
TEST =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = IP_Address_Here)(PORT = Port_Number_Here))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = TEST)
    )
  )

Depending on where the Oracle client is installed, the OCI8 gem might need something like this for installation:

LD_LIBRARY_PATH=/usr/lib/oracle/11.2/client64/lib bundle install

Example output:

{
  :config => {
    :adapter  => "oracle",
    :username => [edited],
    :password => [edited],
    :database => "TEST"
  }
}
{
  :sequel => {
    :owner          => [edited]
    :object_name    => 
    :subobject_name => 
    :object_id      => 
    :data_object_id => 
    :object_type    => 
    :created        => 
    :last_ddl_time  => 
    :timestamp      => 
    :status         => 
    :temporary      => 
    :generated      => 
    :secondary      => 
  }
}
{
  :oci8 => {
    "TWO" => 2
  }
}
{
  :plsql => {
    :owner          => [edited]
    :object_name    => 
    :subobject_name => 
    :object_id      => 
    :data_object_id => 
    :object_type    => 
    :created        => 
    :last_ddl_time  => 
    :timestamp      => 
    :status         => 
    :temporary      => 
    :generated      => 
    :secondary      => 
  }
}
{
  :plsql_output => [
    [0] "Database output will be buffered and can be retrieved",
    [1] "See the get_output method above"
  ]
}

The Ruby filename is capitalized so GitHub places it above Gemfile (it looks like Gists are sorted case-sensitively, uppercase first).

Read more here: https://blog.mikebourgeous.com/2016/12/13/interfacing-legacy-oracle-pl-slash-sql-and-modern-ruby-code/