Groovy Database features oracle

import java.sql.Connection
import java.sql.DriverManager
import javax.sql.DataSource
import groovy.sql.Sql
import oracle.jdbc.driver.OracleTypes

driver = oracle.jdbc.driver.OracleDriver
Connection conn = DriverManager.getConnection(
    'jdbc:oracle:thin:sirtest/sirtest@duck.aplpi.lan:1521:orcl');

/*
 *
 * Here we call a procedural block with a closure.
 * ${Sql.INTEGER} and ${Sql.VARCHAR} are out parameters
 * which are passed to the closure.
 *
 */
Sql sql = new Sql(conn);
def a="foo";
String foo = "x";
println "${a}=${a}"
undefinedVar = null
println """
--Simple demonstration of call with closure.
--Closure is called once with all returned values.
"""
sql.call("begin ${Sql.INTEGER}:=20; ${Sql.VARCHAR}:='hello world';end;") {
   answer,string ->

   println "number=[${answer}] string=[${string}]"
   println "answer is a ${answer.class}";
   println "string is a ${string.class}";
   answer += 1;
   println "now number=${answer}"
   println """[${string.replaceAll('o','O')}]"""
}


/*
 * Here we execute a procedural block. The block returns four out
 * parameters, two of which are cursors. We use Sql.resultSet function
 * to indicate that the cursors should be returned as GroovyResultSet.
 *
 *
 *
 */
println """--next we see multiple return values including two ResultSets
--(ResultSets become GroovyResultSets)
--Note the GroovyResultSet.eachRow() function!!
"""

def tableClosure = {println "table:${it.table_name}"};
println("tableClosure is a ${tableClosure.class}");
String owner = 'SIRTEST';

sql.call("""declare
type crsr is ref cursor;
tables crsr;
objects crsr;
begin
select count(*) into ${Sql.INTEGER} from all_tables where owner= ${owner} ;
open tables for select * from all_tables where owner= ${owner} ;
${Sql.resultSet OracleTypes.CURSOR} := tables;
select count(*) into ${Sql.INTEGER} from all_objects where owner= ${owner} ;
open objects for select * from all_objects where owner= ${owner};
${Sql.resultSet OracleTypes.CURSOR} := objects;
end;
"""
){t,user_tables,o,user_objects ->
	println "found ${t} tables from a total of ${o} objects"
        // eachRow is a new method on GroovyResultSet
	user_tables.eachRow(){x ->println "table:${x.table_name}"}
	user_objects.eachRow(){println "object:${it.object_name}"}
}


/*
 * Determine if we have the stored procedure 'fred' needed
 * for the next test.
 *
 */
Integer procLines = 0
sql.eachRow("select count(*) lines from user_source where name='FRED' and type='FUNCTION'"){
    procLines = it.lines
}

if(procLines ==0) {
  print """
--to demonstrate a function accepting an inout parameter
--and returning a value, create the following function in your schema
create or replace function fred(foo in out varchar2) return number is
begin
foo:='howdy doody';
return 99;
end;
"""

}else{
 /*
  * Here is a call to a function, passing in inout parameter.
  * The function also returns a value.
  */
  println "Next call demonstrates a function accepting inout parameter and returning a value"
  sql.call("{ ${Sql.INTEGER} = call fred(${Sql.inout(Sql.VARCHAR(foo))}) }") {
    answer,string ->
    println "returned number=[${answer}] inout string coming back=[${string}]"
}


println "--Same again, but this time passing a null inout parameter"
sql.call("{ ${Sql.INTEGER} = call fred(${Sql.inout(Sql.VARCHAR(undefinedVar))}) }") {
   answer,string ->
   println "returned number=[${answer}] inout string coming back=[${string}]"
   answer = answer + 1;
   println "Checked can increment returned number, now number=${answer}"
   println """[${string.replaceAll('o','O')}]"""
}

}

/*
 * Finally a handy function to tell Sql to expand a variable in the
 * GString rather than passing the value as a parameter.
 *
 */
["user_tables","all_tables"].each(){table ->
    sql.eachRow("select count(*) nrows from ${Sql.expand table}") {
        println "${table} has ${it.nrows} rows"
    }
}