Straight SQL to Oracle via Ruby Script with RBI

If you have some straight SQL to run against Oracle, and want to execute it in Ruby in a script sans Rails, try DBI. It's one of the older projects, but works fine for purposes of a script.

Put this into a file called sql.rb. This is heavily based on the MySQL example in Using the Ruby DBI Module by Paul DuBois rev. 1.03 (2006-11-28):

#!/usr/bin/ruby -w
# requires you to have previously run:
# gem install dbi
require 'rubygems'
require 'dbi'
puts "Loaded DBI #{DBI::VERSION}"

begin
  #format for Oracle is DBI.connect('DBI:OCI8://host:port/service_name','username','password')
  dbh = DBI.connect('DBI:OCI8://db.acme.org:1234/ACMETEST.WORLD','jdoe','passwd')

  # get server version string and display it
  row = dbh.select_one("select version from v$instance")
  puts "Server version: #{row[0]}"
rescue DBI::DatabaseError => e
  puts "An error occurred"
  puts "Error code: #{e.err}"
  puts "Error message: #{e.errstr}"
ensure
  # disconnect from server
  dbh.disconnect if dbh
end

Then:

chmod +x sql.rb

Modify that file to change the connect string. Notice that the example "ACMETEST.WORLD" is a service_name. So, if you have a service name defined in tnsnames.ora, you could use that, but you don't have to (for example: I just appended ".WORLD" to convert the SID to a service name).

If you haven't already, install dbi:

gem install dbi

Then execute:

./sql.rb

Be sure to use "prepare" to prepare statements if you can't trust the data going into the values in your query. For example:

  prepared_query = dbh.prepare("select email from user where name = ?")
  row = dbh.select_one(prepared_query, name)

For more info, see Using the Ruby DBI Module which provides some info on how to use it.