Oracle DB Object - Gists

Select all invalid objects:


Recompile all invalid objects:




One of the really annoying things about Oracle PL/SQL is the way it tells you something is wrong, gives you a line number and then leaves it up to you to find the statement that caused the error. Well, this little piece of SQL gives you the exact line in error plus the lines immediately before and after it. (Acknowledgements to Ken Atkins of ARIS Consulting).

Initial/Test Data in Grails

When restarting my Grails application in development mode, it was becoming a pain to re-enter the test data to show people how the application worked.

Groovy provides great support for XML, and so using that for loading data was a no brainer.

First step, create a service:

 class LoadDbService {  
      def fileName  
      LoadDbService(def fileName) {  
           this.fileName = fileName  
      }  
      def load (){  
          ... detailed below ...            
      }  
 }  

This service is called from within BootStrap.groovy:

 class BootStrap {  
   def init = { servletContext ->  
           environments {  
                development {  
                     def dbs = new LoadDbService('grails-app/resources/test_data/test_records.xml')  
                     dbs.load()  
                }  
           }            
   }  

This service imports XML data into domain objects. If a domain object refers to another domain object, we user the power of GORM findBy. The implementation of load() is shown below:

 def data = new XmlSlurper().parse(fileName)  
 // These do not have dependencies and can therefore be generated easily  
 data.Release.each {  
      new Release(name: "${it.'@name'}", status: "${it.'@status'}", createdDate: new Date().parseToStringDate("${it.'@createdDate'}")).save(failOnError: true)  
 }  
 data.Stack.each {  
      new Stack(number: "${it.@'number'}").save(failOnError: true)  
 }  
 // for these imports to work correctly they need to search for the items that they relate to using .findByName  
 data.Version.each {  
      new Version(appName: "${it.@'appName'}", tag: "${it.@'tag'}", createdDate: new Date().parseToStringDate("${it.'@createdDate'}"), partOf: Release.findByName("${it.@'partOf'}")).save(failOnError: true)  
 }  
 data.Environment.each {  
      new Environment(hostname: "${it.@'hostname'}", testTier: "${it.@'testTier'}", lastRefresh: new Date().parseToStringDate("${it.'@lastRefresh'}"), ownedBy: Release.findByName("${it.@'ownedBy'}"), partOf: Stack.findByNumber("${it.@'partOf'}")).save(failOnError: true)  
 }       

And finally an example dataset:

Unfortunately, because of the format of XML conflicting with HTML I've had to provide a screenshot.

Inspiration taken from Sanjay Mysoremutt's Blog

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>  

Removing plain text passwords in Gradle

To ensure no plain text passwords are stored on the servers the following piece of code can be used encrypt and decrypt passwords.

 import java.security.*  
 import javax.crypto.*  
 import javax.crypto.spec.*  
 class DESCodec {  
   static encode = { String target ->  
     def cipher = getCipher(Cipher.ENCRYPT_MODE)  
     return cipher.doFinal(target.bytes).encodeBase64()  
   }  
   static decode = { String target ->  
     def cipher = getCipher(Cipher.DECRYPT_MODE)  
     return new String(cipher.doFinal(target.decodeBase64()))  
   }  
   private static getCipher(mode) {  
     def keySpec = new DESKeySpec(getPassword())  
     def cipher = Cipher.getInstance("DES")  
     def keyFactory = SecretKeyFactory.getInstance("DES")  
     cipher.init(mode, keyFactory.generateSecret(keySpec))  
     return cipher  
   }  
   private static getPassword() { "secret12".getBytes("UTF-8") }  
 }  

Things to note in this script are: encode - this method takes a string, encodes the string against a key and returns an encoded string decode - this method takes the encoded string and key and decodes to the plain text password key - this is the string that is set to "secret12" in the code above. This should be changed and owned on a per environment basis. To further improve ease of use this should be parameterised. Usage: task setPassword << { println new DESCodec().encode("password") } task getPassword << { println new DESCodec().decode("VGf1XPEzkT7g6D2EhjMlrg==") } How you use this in your gradle script is entirely up to you. One suggestion I have would be to pass in the Key as a parameter to your script.