For the benefit of my fellow DBAs, here is a quick guide to getting up and running with Scala, a language that is gaining a lot of interest recently. The aim of the exercise is to produce the simplest possible program that can:

1. Read parameters on the command line
2. Connect to Oracle
3. Execute a query
4. Evaluate and print the results
5. Handle any errors encountered along the way

And serve as a basis for further development into useful code, e.g. send an alert by email depending on the value returned, or generate a report or a graph. First, you will need:

• An Oracle client installation (which I expect you already have!)
• A Java Runtime Environment aka JRE (ditto)
• Scala itself in a “batteries included” distribution.
• The text editor of your choice

On a Debian system this is as simple as, as root:

# apt-get install oracle-xe
# apt-get install java
# apt-get install scala
# apt-get install emacs

But many of us have Windows PCs on our desktops, so I will assume that for the rest of this article. If everything is installed correctly, as well as sqlplus in CMD.EXE you should be able to do:

This shows that Java and Scala are installed correctly, and that the Oracle library can be loaded successfully. You may wish to set CLASSPATH permanently into your environment variables. Scala includes an interactive shell, similar to Python, in which commands can be entered and the output displayed. This is often referred to as a REPL and allows for easy experimentation and interactive development. We can see this by importing the necessary functions and connecting to a database:

All very straightforward: val means “value”, which in Scala is a variable that is read-only once set (var is a “traditional” variable that can be modified in-place), lda is a connection to the database (“logon data area” in old-school OCI terminology), with a familiar Oracle connection string with a JDBC prefix. Sometimes people prefer to call this conn instead in their code. sth is a statement handle, and rs is a result set. We iterate over the set using next, getting the 1^st column as a string†. After each command, the Scala REPL displays a text representation of the object that was created by the command, including what type of object it is. Note that although we did not explicitly import Statement or ResultSet we can still use them. Importing simply means that the name doesn’t have to be fully qualified with the java.sql. prefix.

To build a tool, we next need a means to read arguments on the command line. This will also introduce compiling Scala code into a program that can be run (from now on everything is the same on Windows and Linux, assuming the environment is correctly configured). When a Scala program starts up, the command line arguments are supplied to it as an array of strings, passed to a function called main. This is where the program starts. Enter the following into a text editor and save it in your working directory as quickstart.scala:

object Quickstart {
    def main(args: Array[String]) {
        if (args.length != 1) {
            println ("Usage: quickstart <word>");
        } else {
            println (args(0))
        }   
    }
}

Then compile and run it:

C:\Users\gaius\scala>scalac quickstart.scala

C:\Users\gaius\scala>scala -cp . Quickstart
Usage: quickstart <word>

C:\Users\gaius\scala>scala -cp . Quickstart hello
hello

The -cp . option means “use the current directory as the classpath”. In Scala, the main method must be wrapped in an Object, in practice I do this in Python too even though it is optional there, as it promotes code reuse. Now we can merge our two examples so far together into slightly more complex program:

import java.sql.{DriverManager}

object Quickstart {
    def main(args: Array[String]) {
        if (args.length != 1) {
            println ("Usage: quickstart <connect string>");
        } else {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            val lda = DriverManager getConnection "jdbc:oracle:oci:" + args(0);
            val sth = lda createStatement;
            val rs  = sth executeQuery "select 'Hello, world!' from dual";
            while (rs next) {
                println (rs getString 1)
            }
            sth close
            lda close
        }   
    }
}

And run it with the full class path:

A more advanced program would use command line parsing with named arguments, e.g. --username=.... It is not strictly necessary to explicitly close the connection in a program this size as it will happen automagically when the VM exits, but it is a good habit to get into. But we have here the basis of building useful tools: taking parameters, connecting to the database and executing SQL. Now let’s add in doing something with the result set:

/* A simple Scala program to check the free space remaining in a tablespace */

import java.sql.{DriverManager,SQLException, Connection}
import scala.sys.{exit}

object Quickstart {
    def main(args: Array[String]) {
        if (args.length != 3) {
            println ("Usage: quickstart <connect string> <tablespace name> <threshold%>");
            exit;
        }
        
        /* Set the scope of the connection to be outside the try-catch block
           so that it can be referenced in the finally block. In this context
           the type cannot be inferred, so annotate it. */
        var lda:Connection = null
        
        try {
            /* Get the connect string, the tablespace name, and the threshold
               as a number. Will raise an exception if the conversion fails. 
               Similar syntax to Python for multi-assignment */
            val (conn_str, ts_name, ts_threshold) = (args(0), (args(1) toUpperCase), (args(2) toInt))
            
            /* Connect to the database, will raise an exception if this fails */
            Class.forName("oracle.jdbc.driver.OracleDriver");
            lda = DriverManager getConnection "jdbc:oracle:oci:" + conn_str;
            
            /* Prepare a statement and bind in the parameter. Note that we need
               to use brackets if a function takes more than one argument. Note
               syntax for multi-line string, same as Python */
            val sth = lda prepareStatement """SELECT used_percent
                                              FROM dba_tablespace_usage_metrics
                                              WHERE tablespace_name=?"""
            sth setString (1, ts_name);
            
            /* execute the query and make a decision based on the result. I have
               used a boolean condition, but the match-case construct can take any
               sort of condition. Then print a message - note no need to convert the
               integer ts_threshold back into a string before concatenating it. =>
               means "do this and return the result", it is not the same as PL/SQL. */
            val rs = sth executeQuery;
            while (rs next) {
                val used_percent = (rs getInt 1)
                (used_percent >= ts_threshold) match {
                    case false => println ("OK")
                    case true  => println ("Warning, tablespace " + ts_name + " has " + used_percent + "% used")
                }
            } 
        } catch {
            /* Most-specific exception first, so if an expected type of
               exception handle it specially, otherwise fall through to a
               generic handler */
            case e:NumberFormatException => println ("Threshold must be a number.")
            case e:SQLException          => println (e getMessage)
            case e:Exception             => e printStackTrace
        } finally {
            /* If statement checks for the situation that an initial connection
               could not be made to the database. but if we are connected and
               an exception is thrown, cleanly disconnect. Only one statement
               so no need for {} */
            if (lda != null) lda close
        }
    }
}

/* end of file */

See comments in-line. fsc is “fast scala compiler”, which remains running in the background to cut down the startup time on subsequent compiles. Not to be confused with F# compiler also installed in my Windows VM…

Exercise for the reader: Modify this program to print a message if there is no tablespace by that name.

Some final thoughts: Scala is bit more involved to work with than Python, but perhaps it is worth it to have the application and the server administration tools written in the same language‡, in terms of economies of scale and knowledge sharing between developers and DBAs – the DevOps model. Scala is certainly high-level enough to make this viable in a way that it never was with Java. And Scala apps have a longer start up time due to needing the load the JVM first, so it is more suited to longer-running processes, e.g. perhaps it is better to run as a daemon doing many checks, rather than a script for each check invoked every 1 minute or 5 minutes from cron or Nagios.

The next step, if you are interested, should be First Steps to Scala then the Scala tutorial on the website.

† See here for a similar example in OCaml, a language that influenced the design of Scala.
‡ If we do go down the Scala route that is…