Create a gist now

Instantly share code, notes, and snippets.

What would you like to do?
Using the OCI8, Sequel, and ruby_plsql gems all together
#!/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
source 'https://rubygems.org'
gem 'sequel', '~> 4.36.0'
gem 'ruby-oci8'
gem 'ruby-plsql'
gem 'awesome_print'
:adapter: oracle
:username: [user]
:password: [pass]
:database: TEST
# 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)
)
)
Owner

mike-bourgeous commented Aug 16, 2016

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
Owner

mike-bourgeous commented Aug 16, 2016

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"
  ]
}
Owner

mike-bourgeous commented Dec 14, 2016

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment