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)

CREATE FUNCTION getUser

RETURN VARCHAR2 AS

v_retval VARCHAR2(255);

BEGIN

select user into v_retval FROM DUAL;

END;

/

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)

sql.call("{? =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

render("")

}

}

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

http://localhost:8080/yourApp/oracle/getUser


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.