Dance into the Groovy

Groovy (http://groovy.codehaus.org/) is an Object Oriented scripting language for the Java platform. It is dinamically compiled to bytecode for the Java Virtual Machine. It can use and interoperate with any Java class and library (any, not only the ones provided by the JDK) and it has a Java-like syntax: this way its learning curve for a Java depeloper is almost close to zero. Groovy is very powerful and can help in different areas: in some future post I will show and explain better its benefits. In this one I want to show you just a concrete example of use from me in the past. Many times we had to deal with the move of hundreds of Oracle schemas from an envirnoment to another (dev, test, QA, production) or to migrate them from an Oracle release to another. Every time the DBAs regularly forgot to set up the DB links for the new environment. This generated errors that couldn't often be identified quickly. In order to avoid this I wrote a set of Groovy scripts to check the sanity of the DB links that could be automated after any Oracle schema or instance migration. This is a simple example of those scripts (the following code is an oversimplification of the original ones of course). Please read the comment inside the code: they explain everything.

import groovy.sql.Sql
import groovy.sql.Sql.AbstractQueryCommand
import java.sql.ResultSet

def dbLinkHostList = []
// Get a new Groovy Sql instance using the connection parameters for your Oracle schema
def sql = Sql.newInstance("jdbc:oracle:thin:@INSTANCE:1756:SID", "SCHEMA_OWNER",
                      "password", "oracle.jdbc.driver.OracleDriver")


// Execute a query in the system table user_db_links. This query gets all the DB links for your 
// schema
 sql.eachRow("select * from user_db_links") {
    // Put any found DB link name into an array
    dbLinkHostList.add("${it.db_link}");
}

sql.close();


def dbLinksCount = dbLinkHostList.size();
// Cycle between the DB links
for(i in 0..(dbLinksCount - 1)) {
    log.info("dbLink: " + dbLinkHostList.get(i))
    sql = Sql.newInstance("jdbc:oracle:thin:@INSTANCE:1756:SID", "SCHEMA_OWNER",
                      "password", "oracle.jdbc.driver.OracleDriver")
    // Execute a simple query for the DB link
    def sqlConnectionTest = "select rownum id from dual@${dbLinkHostList.get(i)}"
    def AbstractQueryCommand q = sql.createQueryCommand("${sqlConnectionTest}");
     try {
         ResultSet rs = q.execute();
         // Print a message if the DB link is OK for the new environment
         log.info("DB link connection is OK");
         // Don't forget to close the ResultSet
         rs.close();
     } catch(Exception e) {
         // Manage the exception for any broken DB link
         log.info("exception: " + e.getMessage());
     }
     finally {
         // Don't forget to close every DB object used (You don't want to leave open cursors in the 
        // DB, isn't it? :)
         q.closeResources();
         sql.close();
     }
}

This example requires just Groovy and the Oracle JDBC driver in the classpath. A simple solution to solve an annoying problem and save a lot of precious time.

I used a lot of Groovy to wrote useful scripts to automate load testing or functional testing through SoapUI, but this is another story (next...).