Calling Stored Procedures in Groovy

As expected, Groovy provides some compact syntax for calling stored procedures. All the magic is contained in the Sql class.

For example, a stored procedure with the following signature:

insert_book(p_title IN VARCHAR2, p_author IN VARCHAR2, p_isbn OUT VARCHAR2, p_id OUT INTEGER)

Can be invoked with the following Groovy code:

public saveBook(book) {
    def isbn
    def bookId

    Sql sql = new Sql(dataSource)
    sql.call "{insert_book(?, ?, ?, ?)}", 
             [book.title, book.author, Sql.VARCHAR, Sql.INTEGER],
             { spIsbn, spId ->
                 isbn = spIsbn
                 bookId = spId
             }
}

Note:

• the first parameter is a string containing the stored proc call that also parameterizes the in/out variables.
• the second parameter are the variables to be passed in or the corresponding type of the out parameter
• the third parameter is a closure that is given the out parameters of the executed stored procedure in the order they are declared in the sproc.

dataSource is an instance of the data source created in DataSource.groovy. Our snippet assumes the saveBook method is contained in a spring bean, where the dataSource variable can be automatically injected.


More information on Groovy and db objects can be found here.