Skip to content

Instantly share code, notes, and snippets.

Embed
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)
)
)
@mike-bourgeous

This comment has been minimized.

Copy link
Owner Author

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
@mike-bourgeous

This comment has been minimized.

Copy link
Owner Author

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"
  ]
}
@mike-bourgeous

This comment has been minimized.

Copy link
Owner Author

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).

@mike-bourgeous

This comment has been minimized.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.