Accessorize Oracle Database with Ruby


by Dustin Marx

 

Ruby's elegance, simplicity, rich libraries, and strong support for object-oriented features make it an excellent language for full-fledged applications as well as scripting.

Published February 2007

Dynamic scripting languages are enjoying a surge in popularity, because they provide powerful features and enable rapid development. Recent interest in the Ruby on Rails framework has brought Ruby into the forefront for the software development community. However, Ruby can be used for much more than Web development; it also provides database professionals with a powerful, elegant, and flexible scripting language. This article delves into some of Ruby's capabilities and how these features can be leveraged with Oracle Database.

Ruby: A Software Development Gem

Although the Ruby on Rails Web development framework has led to increased interest in Ruby throughout the software development world, Ruby has been around for more than 10 years. It is an object-oriented dynamic scripting language with many built-in features, rich libraries, and a proven track record.

Ruby shares many of the advantages of other dynamic scripting languages, such as Perl and Python, including strong support of regular expressions and dynamic variable and method creation. For those with backgrounds primarily in statically typed languages such as Java, C++, and C#, Ruby provides a great way to learn how to write code in a dynamic language. Ruby is far easier to write and read than Perl.

My colleague Cameron Hendricks has stated, "Perl is the Vegas of programming languages." Dynamic languages sometimes provide major surprises at runtime. Sometimes these are positive and you feel like you have hit the jackpot. Other times the runtime dynamic language surprises are negative, and some of these can be very difficult to debug. Ruby provides many of the potential positives of Perl without as many of Perl's negatives. Ruby feels more predictable to me and less like code development gambling.

This article shows how Ruby can be used interactively and with prewritten scripts to enable common database-related functions. The examples are designed not only to show how Ruby enables simple Oracle database-related scripting but also to demonstrate some of the simple but powerful features of Ruby.

Getting Ruby. Ruby is especially easy to download and install for Microsoft Windows-based platforms. RubyGems is a tool for acquiring and installing updates, extensions, and additional Ruby libraries.

Running Ruby. Once Ruby is downloaded and installed, there are two primary ways to get started. The first approach is to write script files that typically have .rb extensions and run them with the ruby command. The second approach is to use Interactive Ruby (with the irb command) to run Ruby commands interactively from the command line.

If you do not want to install Ruby but would still like to experiment with some basic Ruby syntax, see the interactive Ruby home page. Although you will not be able to run the database-dependent examples in this article at this site, you can play around with any of Ruby's basic built-in functions and syntax.

Hello, Ruby!

Out of the box and without any remarkable syntax, Ruby serves as a nice calculator, especially when you are using Interactive Ruby ( irb) as shown in Figure 1. Figures 1 and 2 demonstrate how to run Interactive Ruby with two different prompt styles (default and simple). Figure 1 demonstrates some of Ruby's mathematical operators, and Figure 2 demonstrates some of Ruby's string operators and conversions between string and numeric formats. You can also try out the operations performed in irb in Figures 1 and 2 at the Ruby home page.

Figure 1

Figure 1 Ruby interpreter and Ruby math operations

Figure 2

Figure 2 Ruby interpreter and Ruby string operations

 

Ruby provides some global constants that can be very useful for writing and maintaining scripts. Figure 3 shows some of these predefined global constants in action. These three global constants indicate the underlying platform, version, and release date for this particular Ruby interpreter. It is not shown here, but you can also take advantage of another predefined global constant, ENV, to see your environment variables set in your operating system. Later in the article, you will see how to use the hash version of ENV.

Figure 3

Figure 3 Useful Ruby constants

 

Figure 4 shows off one more really helpful feature of Interactive Ruby. You cannot see the clicking of the TAB button in this figure, so you'll have to take my word that the TAB button was clicked twice after 0 followed by a period was entered at the IRB prompt. The methods on 0 or other numeric values are then displayed. You can think of this as being similar to method name completion in a Java integrated development environment (IDE) such as JDeveloper. You can see that there are some ceiling (ceil), floor, and round methods for numbers as well as conversion methods such as to_s (to string) and to_a (to array).

Figure 4

Figure 4 Ruby IRB tab completion

 

Ruby and Oracle Database Interaction

To get Ruby to talk to our Oracle database, we will use Ruby/OCI8. This is available from http://rubyforge.org/projects/ruby-oci8/. Although we could write our Ruby code for accessing the database in the interactive IRB, this is a messy approach. The better method is to code our database access code into a Ruby script file and then run that Ruby script file with the ruby interpreter command.

Ruby and Oracle DDL SQL. We begin by creating a Ruby script file to create a new table. We will need to declare that we are using Ruby/DBI with a require statement and then use the Ruby/DBI API. This and the CREATE statement are shown in Listing 1.

Listing 1 createStatesTable.rb
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

Most of the code in Listing 1 is the same SQL code you would use in SQL*Plus or SQL Developer to create a new database table. The database connection string consists of the string DBI:OCI8:ORCL along with the username and password (hr/hr in this case). The DBI portion is standard, the OCI8 portion refers to the Ruby/OCI8 driver, and the ORCL portion refers to the database service. This file, createStatesTable.rb, can be executed from the command line by use of the simple ruby createStatesTable.rb command. If all goes well, no feedback is provided, but the table will exist in the HR schema.

Before moving on to other database operations with Ruby, it is worth pointing out that the ruby interpreter command enables you to check the syntax of your script without actually running it. For example, if I wanted to test the syntax in my createStatesTable.rb script without actually creating that table, I could use the -c option, like this: ruby -c createStatesTable.rb.

Another interesting thing to note about Ruby from Listing 1 is that the variable dbh is defined without a datatype. Like other dynamic scripting languages, Ruby does not require the datatype of variables to be declared but instead assumes the desired datatype based on the context of the variable. In the remainder of the Ruby code listings, we'll see more of this "duck typing"—if it walks and talks like a duck, it must be a duck.

Ruby and Oracle DML SQL. Now that we have a STATES table created, let's populate it. We will insert a few representative states into the table, as shown in Listing 2.

Listing 2 populateStatesTable.rb
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
                            

There are some new features of Ruby/DBI to look at in Listing 2. First, note the use of question marks as placeholders in the INSERT statement. This is useful, because it allows for the statement to be created once and then populated for each respective state inserted into the table. Because the INSERT statement is a DML statement (unlike the DDL CREATE statement in Listing 1), you need a COMMIT call in this listing as well.

With these two Ruby scripts executed, you have a database table called STATES populated with several states. Now let's pretend that you made some classic mistakes on some of the states' capitals and even messed up one state's abbreviation (we used MA for Maine, when MA is really for Massachusetts).

Listing 3 shows the simple UPDATE script to fix those problems. Listing 3 updates the state capitals with placeholders and directly updates the mistaken abbreviation for Maine without the use of placeholders or any type of statement preparation.

Listing 3 updateStatesTable.rb
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

As you might expect, a DELETE is performed very similarly to its fellow DML statements INSERT and UPDATE. Therefore, you will now move on to a SELECT statement, because this statement adds a new wrinkle: the result set from the database query.

Listing 4 demonstrates how to perform a simple query on our newly populated STATES table. The built-in p function prints out the returned rows, as shown in Figure 5. You alternatively could have used the built-in puts method or used the pp ("pretty printer") library to display the query results. Listing 4 shows the use of # to denote inline Ruby comments about these alternative approaches for printing the query results.

Listing 4 queryStatesTable.rb
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    
Figure 5

Figure 5 Query results

 

Ruby DBI provides even fancier formatting for output. Listings 5 and 6 show the use of DBI to output these same query results in tabular and XML formats, respectively. Not only do the code in Listings 5 and 6 show use of DBI::Utils output formatting functionality but they also show slightly different ways of getting and manipulating result sets than did Listing 4.

Listing 5 queryStatesTableFormatter.rb
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    

The tabular output and the XML output produced by these scripts are shown in Figures 6 and 7, respectively.

Figure 6

Figure 6 Tabular output of query results

Figure 7

Figure 7 XML format of query results

 

You have now performed the basic CRUD (CREATE, RETRIEVE, UPDATE, and DELETE) operations with Ruby and Oracle. It is time to move on to some examples of using Ruby with Oracle stored procedures.

Ruby and Oracle Stored Procedures. PL/SQL is a heavily used language in many Oracle Database-based applications. Because significant investment of resources, time, and lessons learned from practical experience with users has been invested in Oracle stored procedures, it is vital that any language you use can access that wealth of proven functionality.

Listing 7 shows code for accessing the PL/SQL built-in stored procedure DBMS_UTILITY.DB_VERSION, which accepts two OUT parameters, and your code displays the results passed back via these OUT parameters.

Listing 7 builtInDBVersionCompat.rb
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    

The displayed results of running this Ruby code are shown in Figure 8.

Figure 8

Figure 8 Results of Ruby's accessing built-in DBMS_UTILITY.DB_VERSION

There is another interesting observation to make about Listing 7. This listing demonstrates the use of Ruby symbols (:bind_value in this case) and also shows use of the DBI::Handle (named sth_db in this case) func method to call functionality specific to this Ruby DBI database driver.

The code in Listing 8 demonstrates the use of Ruby DBI to run the built-in stored function DBMS_METADATA.GET_DDL. This built-in function is useful for several reasons, one of which is as an example here of how to get the returned value from a function in Ruby DBI. The function requires that the object type and object name be passed in along with several optional parameters. In this case, you passed in only the two required parameters and used name notation to pass them.

Listing 8 builtInGetDDL.rb
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

The DBMS_METADATA.GET_DDL function returns the DDL for creating the table we specified. Note that this is useful information because it provides us the DDL we would need to re-create our table. The returned DDL is significantly more specific than our CREATE TABLE statement, because it explicitly spells out settings we assumed. Viewing these results can provide greater insight into the workings of the Oracle database and help database professionals decide which parameters to change and tune.

Besides showing how to access a return value from an Oracle stored function, Listing 8 shows a different method for binding variables than we used earlier. In our previous listing with bind variables (Listing 7), we used positional binding, which meant that you placed question marks (?) in the database execution string and used consecutive integers beginning with 1 to reference the values. In Listing 8, you used name binding, meaning that we placed Ruby symbols (such as :out1, :in1, and :in2 in this case) and referenced these bind variables by name later to access their values. Ruby's support for positional versus name binding is similar to PL/SQL's support for positional parameter notation versus named parameter notation. Figure 9 shows the results of running Listing 8—the DDL for creating our STATES table.

 

Figure 9

Figure 9 DDL create statement from DBMS_METADATA.GET_DDL



Ruby and Oracle: Not Everything Is in the Database

The interest of Oracle professionals is not limited to Oracle Database; there are configuration files, environment settings, and even other Oracle products (such as Oracle Application Server) that deserve attention. Let's look at a few examples of using Ruby for these areas.

Ruby and the Environment. Ruby provides easy access to the operating system's environment variables. Listing 9 shows a simple Ruby program for providing users with a list of their defined environment variables.

Listing 9 displayOracleENV.rb
ENV.each {|key ,value| print key, "=", value, "\n"}      

This single line of code iterates over the ENV hash by using the each method and prints one environment variable with its value per line. This one-line example provides further evidence of the power and conciseness of the Ruby language.

Ruby and Operating System Files. It can be useful for an Oracle Database professional to access the contents of various configuration and other files, such as the init.ora file. Ruby makes it really easy to view key-value settings in this file, as shown in Listing 10.

Listing 10 displayInitOra.rb
=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)

Listing 10 demonstrates several features of Ruby. Perhaps the most important Ruby feature demonstrated in this listing is the use of Regexp for regular expression handling. An instance of Regexp is instantiated with the desired pattern (^.+=.+$) for matching strings. That regular expression is applied to the string's scan method to return substrings in that string matching the regular expression provided. Ruby, like Perl, has fabulous regular expression support.

Listing 10 also demonstrates the acquisition of input from the command line and from console-based user prompt. The code first uses the predefined Ruby variable ARGV to attempt to get the file path and filename from the command line. If this does not work, the code then prompts the user for the path. If the user does not provide a path and filename, a hard-coded path (including filename) is used. These conditionals also show the if statement and the Perl-like unless statement.

The code in Listing 10 shows the chomp! method in action. This String method is used to remove the record separator from the end of the string-based filename retrieved from user console input. If this were not done, the newline from the gets input would cause trouble when trying to open a File object based on that filename. The exclamation point on a Ruby method indicates that the method is doing something potentially dangerous to the object. Similarly, Ruby supports methods ending in a question mark (?) to indicate a "question" method returning a true or false result.

The final example of this section looks at a simple Ruby script that compares two init.ora files and reports the differences between them. Listing 11 uses many of the same approaches as those in Listing 10 but reads in the contents of two init.ora files passed in as command-line arguments.

Listing 11 diffInitOra.rb
=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

This example shows use of the pp library to print out the files' names and, if either of the files is not still open, the fact that the file is closed. Most important, this code listing shows off Ruby's powerful subtraction operator in action. With the simple use of a minus sign between two strings read from the file, the differences between the two files can be easily determined.

For purposes of the example related to Listing 11, I copied an existing init.ora file into a second copy and then changed three parameters in the second copy (open_cursors, db_name, job_queue_processes). To make things more interesting, I also rearranged the order of the parameters in the second copy of the init.ora file. Figure 10 shows the results of running the code in Listing 11 on these two init.ora files. This simple code does all the heavy lifting and returns exactly the differences we know to be in the files, even with the second file rearranged.

 

Figure 10

Figure 10 Differences between two init.ora files



As these examples have shown, Ruby makes file manipulation simple and powerful. Ruby can similarly be used with spooled output and other types of files useful to Oracle professionals.

Ruby Exception Handling

Ruby has several powerful features that can be used in both scripts and in object-oriented applications. One of these features is Ruby's exception handling. Listing 12 contains examples of using Ruby's exception handling mechanism.

Listing 12 rubyExceptions.rb
=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)

Listing 12 demonstrates use of the begin keyword to mark the beginning of a code block from which an exception might be raised (thrown). The keyword raise is used in this listing for the script to raise (throw) its own exception. The keyword rescue is used multiple times and is similar to Java's catch keyword. Ruby's ensure keyword is similar to Java's finally keyword and is used in this code listing to perform functionality regardless of whether an exception is encountered or not.

As shown in Listing 12, Ruby also supports the else keyword to cover functionality that should be performed if no exception is thrown for a particular block of code denoted by begin and end. Comments in the code listing further explain Ruby's exception handling capabilities.

Figure 11 displays the results of running the script in Listing 12 three times. An unknown file name is intentionally provided the first time the script is run to invoke the file handling exception handling. The script is run again with the correct file name to demonstrate execution of the else block and to allow execution to move onto the block of code related to regular expression handling. In the second execution of the script, an improper regular expression is provided in order to demonstrate handling of RegexpError. The third run of this script demonstrates the script running through without a raised exception. All three runs demonstrate execution of the ensure block because that block is always called in all exception handling regardless of whether an exception is raised (thrown) or not.

 

Figure 11

Figure 11 Ruby exceptions in action



Ruby DBI Exception Handling. The last section introduced Ruby exception handling. Ruby/DBI provides its own exception handling hierarchy that is demonstrated in Listing 13. In practice, it is more common to only capture the "database catch all" exception DBI::DatabaseError and access its attributes as shown in Listing 13 to print out the database error code and error message.

Listing 13 rubyDbExceptions.rb
=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      

The other DBI exceptions shown in Listing 13 include those derived from DBI::DatabaseError as well as the DBI::InterfaceError exception for catching exceptions related to the DBI interface rather than to the database.

The code in Listing 13 will cause an exception to be thrown because there is a "while 0" loop (a 0 is evaluated in Ruby as "true") that will repeat until the exception is thrown. The exception does get thrown because DBI.connect is repeatedly called without properly closing the opened connections until the DBI::DatabaseError is thrown. In this case, the displayed error code is 12520 (an Oracle-specific database error code) and the error string is "ORA-12520: TNS:listener could not find available handler for requested type of server." These output code and string values are output using the appropriate attributes of DBI::DatabaseError (err and errstr).

If the while loop in Listing 13 that intentionally forces a database error was replaced with a working database operation (such as a SELECT statement), the rescue, else, and ensure blocks would be activated similarly to those in Listing 12. Any exception still occurring (such as an integrity constraint) would lead to the appropriate "rescue" block being invoked. If no exception for which a rescue block is designated is encountered, the code in the "else" block would execute and commit the transaction. In all cases, whether an exception is thrown or not, the "ensure" block of code will be executed, disconnecting the handler appropriately.

When writing Ruby scripts, you do not necessarily need to catch (rescue) exceptions (think Java unchecked exceptions). This is illustrated by all of the code listings before Listing 12 that did not have exception handling. However, if you know certain exceptions are likely and there is something you would like your script to do about these cases (such as exiting immediately or printing out certain exception-related details), Ruby makes exception handling easy. If you do not associate rescue-else-ensure blocks with a code block in which an exception is thrown, the script will abruptly stop execution in that block, the general exception information will be displayed, and the next code block will be executed.

Figure 12 shows some of Ruby's main exceptions. Figure 13 shows Ruby DBI exceptions. You can always create and use your own Ruby exceptions in addition to these standard exceptions as well.

 

Figure 12

Figure 12 Selected Ruby exceptions

Figure 13

Figure 13 Ruby/DBI exceptions



Because Ruby, like Java, is an object-oriented language, I was able to create the class diagrams in Figures 12 and 13 with Oracle JDeveloper 10g's UML modeling tool. The DatabaseError attributes are called out specifically in Figure 13 because they are used in the code listing.

Ruby and Object-Relational Mapping

In this article, you have learned how Ruby can be used in conjunction with Ruby/DBI to perform database operations. For much of the direct database scripting we find ourselves needing to do, this is usually a reasonable approach, but if you desire an object-relational mapping approach with Ruby and your Oracle database, ActiveRecord is probably what you are looking for.

ActiveRecord was introduced with Ruby on Rails, but it is now available for use outside of Rails. ActiveRecord relies on many conventions and assumptions to make the ORM experience relatively simple when these conventions are followed. Most of the ActiveRecord conventions spell out appropriate naming of Ruby model classes and Oracle database tables and their columns. ActiveRecord also provides simple override mechanisms for deviating from the conventions and assumptions as necessary.

ActiveRecord provides a noteworthy example of Ruby's object-oriented capabilities and especially of its support for inheritance and mix-ins. Developers using ActiveRecord often have to add very little code to their ActiveRecord model classes because of the significant functionality made available by the base ActiveRecord class that all models inherit from.

Ruby and Web Development

With the wide popularity Ruby of Rails has enjoyed recently, it is difficult to believe that there are any other Web frameworks for Ruby. It turns out that there is more than one Web framework available for it, but Rails is certainly the dominant player in that field. In fact, Ruby on Rails has not only brought Ruby newfound fame but has also led to imitation by like-minded frameworks for other languages such as Python and Java. It is likely that if you are going to do Web development with Ruby, you will want to use Ruby on Rails.

Ruby Is More Than Just a Scripting Language

This article has focused on Ruby's scripting abilities related to database access and database-related file manipulation. Ruby, however, provides much more than this. Ruby's elegance, simplicity, rich libraries, and strong support for object-oriented features make it an excellent language for full-fledged applications as well as scripts.

With or without Rails, Ruby is a highly useful language worth learning and using. Although it can be used to develop sophisticated object-oriented applications and Web applications, it still retains the powerful features of a scripting language that make it a great fit for scripting related to Oracle Database and other Oracle tools.


Dustin Marx is a senior software engineer and architect at Raytheon Company.
Send us your comments