Sunday, May 2, 2010

Grails and PL/SQL - how to integrate PL/SQL into Grails

My experience has been with data centric applications. The business logic is typically stored in the database.

Recently, I have been working with Grails, which is a great Java framework. But as with most Java frameworks, its support of Oracle is mainly centered around Hibernate. Hibernate definitely has advantages, but if you're trying to develop a robust Oracle/data centric application using PL/SQL to manage business logic, its not the best solution.

I have been working at trying to create a plugin to allow developers to better integrate PL/SQL into Grails. What I've come up with is to use Grails services to call PL/SQL procedures. My approach is in its early stages, but I've found its pretty easy to call PL/SQL procedures and functions.

The first step is to create an 'Oracle' service.

1) grails create-service Oracle

This will create a default OracleService in the grails-app/services

2) In your Oracle schema, add the following test function

(This is just a test function)



v_retval VARCHAR2(255);


select user into v_retval FROM DUAL;



3) Modify the OracleService.groovy file to look like the following

import groovy.sql.*

class OracleService {

def dataSource // using the datasource we define in the spring's resources.xml

boolean transactional = true

def serviceMethod() {


/*Example of Oracle function */

def getUser = {

def retval

Sql sql = new groovy.sql.Sql(dataSource)"{? =call getUser()}",[Sql.VARCHAR]) { user -> retval=user}

return retval



4) Add the following to whatever controller you want to call the Oracle function from

class OracleController {

def oracleService

def getUser={

def mysid = oracleService.getUser()

println "SID="+mysid




5) Note there is no view in my example.. but you can test it as follows:


If it works you should see your Oracle login printed to the console

Obviously this is just an example. But it demonstrates how easy it is to integrate Oracle packages into a controller. From here we can write our business logic, and easily call Oracle from our controllers.