16-5. Accessing PL/SQL from Groovy
Problem
You are writing a Groovy program and want to call some PL/SQL stored procedures or functions from it.
Solution
Use GroovySQL to establish a database connection, and make the call to the PL/SQL stored program. For example, here's how you would use of GroovySQL to connect to an Oracle Database and call a PL/SQL function:
import groovy.sql.Sql
import oracle.jdbc.driver.OracleTypes
Sql sql = Sql.newInstance("jdbc:oracle:thin:@hostname:1521:dbname",
"username","password","oracle.jdbc.driver.OracleDriver")
dept_id = 50
sql.call('{? = call calc_quarter_hour(?)}', [Sql.DOUBLE, 6.35]) { qtr_hour->
println qtr_hour
}
Short and to the point, the Groovy script in this example connects to an Oracle Database, executes a PL/SQL function call, returns a value, and prints the result.
How It Works
Groovy is a unique JVM language that is useful for developing productive and efficient applications. It can be used for developing a wide variety of applications, from scripts to enterprise-level web applications. The syntax of the Groovy language is unlike that of other languages on the JVM because the Groovy compiler allows you to write Java code and it will be deemed as valid Groovy. However, Groovy also has its own syntax that can be combined with Java syntax if you want to do so. Its flexibility allows for beginners to pick up the language as they go and allows advanced Groovy coders to write code in Groovy syntax that is magnitudes smaller than the amount of lines taken to write the same code in Java.
In the solution to this example, the Groovy SQL API is used to connect to an Oracle Database and issue a PL/SQL function call. The top of the script contains imports statements. The imports in Groovy work in the same manner as Java imports. The groovy.sql.Sql import pulls all the Groovy SQL functionality into the script. The second import is used to pull in the Oracle driver.
The database connection is made by using the Sql.newInstance method and passing the JDBC URL for the database along with the user name, password, and database driver class. The actual PL/SQL function call occurs with the Sql instance's call() method, and the syntax is very similar to that of Java's JDBC API, whereas you pass a string that is enclosed in curly braces in the following format. The following example demonstrates a call to the CALC_QUARTER_HOUR PL/SQL function that was written in Recipe 4-1:
{? = call calc_quarter_hour(?)}
The question mark characters (?) correlate to bind variables. The second argument that is passed to the call() method is a list of parameters including the return type and value of the parameter that will be passed to the PL/SQL function. In this case, the PL/SQL function's return type is groovy.sql.Sql.DOUBLE, and the value that will be passed to the function is 6.35. The code that follows the call is some Groovy syntactic sugar and is otherwise known as a closure. By specifying curly braces ({}) after the function call, you are telling Groovy to pass any return values to the variable contained within the braces. In this case, qtr_hour will contain the result from the PL/SQL function call, and it prints the result upon return via use of the closure -> notation and then specifying a print statement afterward.
If you have never seen Groovy code before, this syntax will seem a bit awkward. However, once you become used to the syntax, it will become a powerful asset to your tool box. It is easy to see that taking standard Java JDBC implementations for accessing PL/SQL and translating them into a different language will allow for the same PL/SQL connectivity across most languages that run on the JVM. For more information regarding the use of Groovy, Groovy SQL, or closures in Groovy, please see the online documentation at http://groovy.codehaus.org/Beginners+Tutorial.