Automating Unit Tests for PL/SQL Using Gradle

Gradle strikes again:


Yes, it really was that easy!

The Oracle database
This post assumes that you have an Oracle database available. I used an Oracle 10g XE instance running on localhost to develop this post. XE is free to download and is sufficient for development purposes.

I had a SCOTT schema with the password "oracle" in the database and also installed the utPLSQL schema, which is a necessary step to following this example. To replicate what I done I recommend downloading the Oracle Developer Days VM.

NOTE: If you install the utPLSQL schema into an Oracle 10g XE database, make sure that you have granted access on UTL_FILE to public as explained here (remember to connect as sysdba when doing this otherwise it won’t work).

The structure I used for this example is:

.
|-build
|---build.gradle
|---ut_run.sql
|-src
|---main
|-----sql
|-------run.sql
|---test
|-----sql
|-------run.sql

Running the JGitFlow library from within Gradle

I recently seen this blog post and thought it would be really cool if I could integrate git flow into my build scripts. Whats great about this is that because this is a java library it was extremely painless to integrate.

With a little effort this could easily be converted into a plugin for Gradle, but here's my example:


This is only a snippet, so not all of the library is shown, but it works very well as a proof of concept.

Oracle Linux 5.5 with Guest Additions - VirtualBox

I wanted a sandbox VM, but for it to replicate the production servers it needed to be based on Linux 5.5. As I was running this on my local machine in VirtualBox I also wanted Guest Additions installed.

VirtualBox 3.1: Beginner's Guide is a good book to start with if you're new to VirtualBox and want to know how to create virtual machines on your desktop.

So here's how to get Guest Additions installed:

1. Download and Install Oracle Linux
2. Download and copy the appropriate yum configuration file in place, by running the following command as root:

# cd /etc/yum.repos.d
# wget http://public-yum.oracle.com/public-yum-el5.repo

Guest Additions requires the following in order to install successfully:

kmod-ovmapi-uek
libovmapi
libovmapi-devel
ovmd
python-simplejson
xenstoreprovider
ovm-template-config
ovm-template-config-authentication
ovm-template-config-datetime
ovm-template-config-firewall
ovm-template-config-network
ovm-template-config-selinux
ovm-template-config-ssh
ovm-template-config-system
ovm-template-config-user

These can be downloaded by running the following command once the yum configuration is in place:

# yum install libovmapi xenstoreprovider ovmd python-simplejson xenstoreprovider

Gradle/Groovy - get Git build meta information

When creating a release package its nice to maintain a copy of all the metadata. I like to do this using the power of Groovy from within my Gradle build scripts.

 import groovy.xml.MarkupBuilder  
 task xmlGen << {  
      def tag = "git describe --abbrev=0 --tags".execute().text  
      def sha = "git rev-parse HEAD".execute().text  
      def fw = new FileWriter("build_info.xml" )  
      def xml = new groovy.xml.MarkupBuilder(fw)  
      xml.build(id:tag){  
           ProjectName("ORCA")  
           SHA1(sha)  
           Date(new Date())  
           Components("component1, component2, component3")  
      }  
 }  

And the output is:

 <build id="v1.3.0">  
  <ProjectName>MyProject</ProjectName>   
  <SHA1>1f661e69797dc23281d2955d6ca2dda3cdd81dc0</SHA1>   
  <Date>Thu May 02 16:14:33 BST 2013</Date>   
  <Components>component1, component2, component3</Components>   
 </build>