Groovy Database features oracle

import java.sql.Connectionimport java.sql.DriverManagerimport javax.sql.DataSourceimport groovy.sql.Sqlimport oracle.jdbc.driver.OracleTypesdriver = oracle.jdbc.driver.OracleDriverConnection conn = DriverManager.getConnection(    'jdbc:oracle:thin:sirtest/[e-mail]: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 = nullprintln """--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("""declaretype crsr is ref cursor;tables crsr;objects crsr;beginselect 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 GroovyResultSetuser_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 = 0sql.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 schemacreate or replace function fred(foo in out varchar2) return number isbeginfoo:='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"    }}