require 'dbi' 

dbh = DBI.connect('DBI:OCI8:ORCL', 'hr', 'hr') 

dbh.do("CREATE TABLE states ( 

id CHAR(2) PRIMARY KEY, 

name VARCHAR2(15) NOT NULL, 

capital VARCHAR2(25) NOT NULL)") 

dbh.disconnect
            

            
require 'dbi' 

dbh = DBI.connect('DBI:OCI8:ORCL', 'hr', 'hr') 

sqlInsert = "INSERT INTO states (id, name, capital) 

VALUES (?, ?, ?)" 

dbh.do(sqlInsert, "AL", "Alabama", "Birmingham") 

dbh.do(sqlInsert, "AZ", "Arizona", "Phoenix") 

dbh.do(sqlInsert, "CO", "Colorado", "Denver") 

dbh.do(sqlInsert, "FL", "Florida", "Tallahassee") 

dbh.do(sqlInsert, "MA", "Maine", "Augusta") 

dbh.do(sqlInsert, "PA", "Pennsylvania", "Philadelphia") 

dbh.do(sqlInsert, "UT", "Utah", "Salt Lake City") 

dbh.do(sqlInsert, "WA", "Washington", "Seattle") 

dbh.do(sqlInsert, "WY", "Wyoming", "Cheyenne") 

dbh.commit 

dbh.disconnect
            

            
require 'dbi' 

dbh = DBI.connect('DBI:OCI8:ORCL', 'hr', 'hr') 

sqlCapitalsUpdate = "UPDATE states SET capital = ? WHERE id = ?" 

dbh.do(sqlCapitalsUpdate, "Montgomery", "AL") 

dbh.do(sqlCapitalsUpdate, "Harrisburg", "PA") 

dbh.do(sqlCapitalsUpdate, "Olympia", "WA") 

dbh.do("UPDATE states SET id = 'ME' WHERE name = 'Maine'") 

dbh.commit 

dbh.disconnect
            

            
require 'dbi' 

dbh = DBI.connect('DBI:OCI8:ORCL', 'hr', 'hr') 

rs = dbh.prepare('SELECT * FROM states') 

rs.execute 

while rsRow = rs.fetch do 

p rsRow 

#Alternative output: puts rsRow 

#Alternative output: pp rsRow 

end 

rs.finish 

dbh.disconnect
            

            
require 'dbi' 

dbh = DBI.connect('DBI:OCI8:ORCL', 'hr', 'hr') 

rs = dbh.execute('SELECT * FROM states') 

rows = rs.fetch_all 

column_names = rs.column_names 

rs.finish 

DBI::Utils::TableFormatter.ascii(column_names, rows) 

dbh.disconnect 

Listing 6: queryStatesTableXML.rb 

require 'dbi' 

dbh = DBI.connect('DBI:OCI8:ORCL', 'hr', 'hr') 

rs = dbh.execute('SELECT * FROM states') 

states_rows = rs.fetch_all 

rs.finish 

DBI::Utils::XMLFormatter.table(states_rows) 

dbh.disconnect
            

            
require 'dbi' 

db_read_str = 'BEGIN DBMS_UTILITY.DB_VERSION(?, ?); END;' 

dbh = DBI.connect('DBI:OCI8:ORCL', 'hr', 'hr') 

sth_db = dbh.prepare(db_read_str) 

sth_db.bind_param(1, ' ' * 50) # allow for up to 50 chars 

sth_db.bind_param(2, ' ' * 50) # allow for up to 50 chars 

sth_db.execute 

version = sth_db.func(:bind_value, 1) 

puts "Oracle DB Version: " + version 

compatibility = sth_db.func(:bind_value, 2) 

puts "Oracle DB Compatibility: " + compatibility 

dbh.disconnect
            

            
require 'dbi' 

db_read_str = 'BEGIN :out1 := DBMS_METADATA.GET_DDL(object_type=>:in1, ' 

db_read_str += 'name=>:in2); END;' 

puts db_read_str 

dbh = DBI.connect('DBI:OCI8:ORCL', 'hr', 'hr') 

sth_db = dbh.prepare(db_read_str) 

sth_db.bind_param("out1", ' ' * 20000 ) 

sth_db.bind_param("in1", "TABLE") 

sth_db.bind_param("in2", "STATES") 

sth_db.execute 

puts sth_db.func("bind_value", "out1") 

dbh.disconnect
            

            
ENV.each {|key ,value| print key, "=", value, "\n"}
            

            
=begin 

This Ruby script demonstrates several Ruby features while performing the 

functionality of returning values and their setting from the init.ora file. 

This multiline comment block is demarcated by the =begin and =end. 

Normal, single-line comments are also shown in this code (pound sign). 

=end 

fileName = ARGV.to_s 

# example of if statement 

if fileName.size < 1 

print "Enter init.ora path and name: " 

fileName = gets.chomp! 

end 

#example of unless statement 

unless fileName.size > 1 

fileName = "C:\\oracle\\product\\10.1.0\\admin\\orcl\\init.ora" 

end 

print "File Name: " + fileName + "!" 

theFile = File.new(fileName, "r") # read the file 

text = theFile.read # copy file's contents into string 

theFile.close # string holds file contents; close file 

regExp = Regexp.new('^.+=.+$') 

puts text.scan(regExp)
            

            
=begin 

This script compares two files and returns from each file what it has that 

the other provided file does not have. Original order of either file does 

not matter. 

=end 

require 'pp' 

unless ARGV.size == 2 

print "Usage: ruby arrayMagic.rb firstFile secondFile" 

exit 

end 

firstFile = File.new(ARGV[0], "r") 

secondFile = File.new(ARGV[1], "r") 

regExp = Regexp.new('^.+=.+$') 

text1 = firstFile.read.scan(regExp) 

text2 = secondFile.read.scan(regExp) 

firstFile.close 

pp firstFile 

puts text1-text2, "\n" 

pp secondFile 

puts text2-text1 

secondFile.close
            

            
=begin 

This script is similar to Listing 10, but demonstrates Ruby exception handling 

using functionality originally demonstrated in Listing 10. 

=end 

fileName = ARGV.to_s 

#obtain file name if not provided on command-line 

if fileName.size < 1 

print "Enter init.ora path and name: " 

fileName = gets.chomp! 

end 

#ensure something has been provided for file name 

begin 

if fileName.size < 1 

raise ArgumentError, "No valid file name provided" + "\n" 

end 

rescue ArgumentError => argErr 

print argErr 

raise # re-raise this exception and force script termination 

end 

#get file contents 

begin #Begin exception handling block for file I/O 

theFile = File.new(fileName, "r") 

text = theFile.read 

theFile.close 

rescue IOError 

print "I/O Error: Problem accessing file " + fileName + "\n" 

exit 

rescue Errno::ENOENT 

print "ENOENT: Cannot find file " + fileName + "\n" 

exit 

rescue Errno::EPERM 

print "EPERM: Insufficient rights to open " + fileName + "\n" 

raise 

rescue Exception # Catch-all: More exceptions captured than with "rescue" alone 

print "Generic error rescued (captured) during file I/O attempt." + "\n" 

raise 

else 

print "Good news! There was no problem with file I/O for " + fileName + "\n" 

ensure 

print "Good or bad, file handling attempt is now complete!\n" 

end #End exception handling block for file I/O 

#obtain text string for regular expression 

print "Enter regular expression pattern: " 

pattern = gets.chomp! 

begin #Begin exception handling block for regular expression 

regExp = Regexp.new(pattern) 

rescue RegexpError 

print "Problem with regular expression " + regExp.to_s + "\n" 

exit # Nothing to be done with a bad regular expression 

ensure 

print "Regular expression evaluated was: " + regExp.to_s + "\n" 

end #End exception handling block for regular expression 

puts text.scan(regExp)
            

            
=begin 

Listing13-rubyDbExceptions.rb 

This script demonstrates the use of exceptions with DBI::DatabaseError 

and database-related exception classes that inherit from DBI::DatabaseError. 

Often, it is enough to capture DatabaseError, but this example shows all 

of the inherited exception classes. DatabaseError is rescued last so that 

a more particular database-related exception might be rescued first. 

This "try" block of this code includes an "infinite" while loop that will 

open connections without closing them until a DBI::DatabaseError is forced. 

=end 

require 'dbi' 

begin 

counter = 0 

while 0 # "infinite" loop because 0 resolves to "true" for Ruby conditional 

dbh = DBI.connect('DBI:OCI8:ORCL', 'hr', 'hr') 

counter += 1 

puts "DB Connection #" + counter.to_s + "\n" 

#Intentionally NOT closing with dbh.close to force DatabaseError. 

end 

rescue DBI::DataError => dataErr 

dbh.rollback 

puts "DB error due to problem with data" 

puts "Error Code: #{dataErr.err}" 

puts "Error Message: #{dataErr.errstr}" 

puts "DB rollback.\n" 

rescue DBI::IntegrityError => integErr 

# Example: Trying to insert same value for unique column twice. 

dbh.rollback 

puts "DB error due to integrity problem." 

puts "Error Code: #{integErr.err}" 

puts "Error Message: #{integErr.errstr}" 

puts "DB rollback.\n" 

rescue DBI::InternalError => internErr 

dbh.rollback 

puts "DB error database internal error." 

puts "Error Code: #{internErr.err}" 

puts "Error Message: #{internErr.errstr}" 

puts "DB rollback.\n" 

rescue DBI::NotSupportedError => notSuppErr 

dbh.rollback 

puts "DB feature not supported." 

puts "Error Code: #{notSuppErr.err}" 

puts "Error Message: #{notSuppErr.errstr}" 

puts "DB rollback.\n" 

rescue DBI::OperationalError => opErr 

dbh.rollback 

puts "DB error due to problems with operation of database." 

puts "Error Code: #{opErr.err}" 

puts "Error Message: #{opErr.errstr}" 

puts "DB rollback.\n" 

rescue DBI::ProgrammingError => dbProgErr 

# Example: Bad column name in SQL statement. 

dbh.rollback 

puts "DB error due to programming problem.\n" 

puts "Error Code: #{dbProgErr.err}" 

puts "Error Message: #{dbProgErr.errstr}" 

puts "DB rollback.\n" 

rescue DBI::DatabaseError => dbErr 

# Catch-all for all database exceptions. 

dbh.rollback 

puts "Database exception encountered." 

puts "Error Code: #{dbErr.err}" 

puts "Error Message: #{dbErr.errstr}" 

puts "DB rollback." 

rescue DBI::InterfaceError => ifError 

dbh.rollback 

puts "Problem with DBI interface encountered." 

rescue RuntimeError 

dbh.rollback 

puts "Unknown error (not DB or DBI) encountered." 

else 

puts "DB commit.\n" 

dbh.commit 

ensure 

puts "Disconnecting database handler." 

dbh.disconnect 

end