Automating Unit Tests for PL/SQL Using Gradle

Gradle strikes again:

	ext{
	dbun = "SCOTT"
	dbpw = "oracle"
	dbinst = "orcl"
	}
	task deployUnitTests << {
	def sqlplusCmd = "sqlplus -s $dbun/$dbpw @../src/test/sql/run.sql".execute()
	sqlplusCmd.in.eachLine { line -> println line }
	}
	task runUnitTests << {
	def sqlplusCmd = "sqlplus -s $dbun/$dbpw @ut_run.sql".execute()
	sqlplusCmd.in.eachLine { line -> println line }
	}
	task deployDBUpdate << {
	def sqlplusCmd = "sqlplus -s $dbun/$dbpw @../src/main/sql/run.sql".execute()
	sqlplusCmd.in.eachLine { line -> println line }
	}

view raw utplsql-build.gradle hosted with ❤ by GitHub

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