“There’s ways to amuse yourself while doing things and that’s how I look at efficency.” | ||
| --Donald Knuth | ||
In the previous chapters, we have seen a few examples of what you can do with Java in the database, beyond the traditional stored procedures. Oracle offers only PL/SQL and Java for database programming; however, with the emergence of Java compilers for non-Java languages (see Programming Languages for the Java Virtual Machine[1]), it is tempting to reuse the Java VM to run bytecode resulting from the compilation of non-Java languages in the database; the SQLJ language covered in Part III is a brilliant illustration of this approach. This is exactly what some of you did; this chapter reports basic proof of concepts running JACL (i.e., Java runtime for TCL), JYTHON (i.e., Java runtime for PYTHON), Kawa (i.e., Java runtime for Scheme), and Groovy in the database. These experimentations are foreign to Oracle and do not prefigure any plan to support any of the languages mentioned here in the Oracle database.
Cost reduction is driving the need to improve application developer productivity (i.e., reuse skills, applications, libraries, APIs) and deployment flexibility (partitioning applications across tiers). Java fulfills such requirement by enabling the reuse of applications, libraries, and developer skills across vendors and also across the middle tier and the database. However, apart from Java, existing procedural languages[2] are not reusable across the middle tier and the database tier. Let’s look at the offerings in this area of reusing non-Java languages in the database.
The implementation of a Common Language Runtime (CLR) in SQL Server 2005, IBM DB2 release 8.2 and up, and Oracle Database 10 g Release 2[3] allows programmers to use .NET-supported languages (e.g., C#, VB.NET, J#) across the Windows platform. (i.e., the middle tier and database). CRL coverage is beyond the scope of this book but is mentioned as mechanism allowing language support across tiers, beyond Java.
Open source databases such as MySQL and PostgreSQL offer (or are in the process of offering) basic support for popular Web scripting languages such as Perl, PHP, and Python, allowing these languages to be used across both middle tiers and databases. In section 5.2, you’ll see proofs of concept of running TCL, Python, Scheme, and Groovy in the Oracle database.
Perl is a popular scripting language backed by a large developer community, the Comprehensive Perl Archive Network (CPAN). MyPerl is a Perl interpreter for MySQL.[4] Similarly, MyPHP allows implementing PHP user-defined functions in MySQL.
According to its documentation,[5] besides its own proprietary procedural language (PL/pgSQL), the PostgreSQL database provides support for Java, as well as for popular scripting languages such as PHP, Perl, Python, TCL, Ruby, and Mono.
I coined the term database scripting to designate the ability to run scripting commands in the database, similar to SQL. How can OracleJVM help support non-Java languages or scripting languages? The trick is to rely on compilers that generate Java bytecode for non-Java languages and host the corresponding Java runtime of these languages in OracleJVM. You can view this as an open and broader-reach alternative to CLR; however, to my knowledge, Oracle has no plan to support such an approach. The following experiences are proofs of concept that you can implement yourself, in a nonproduction database environment; it is up to you to use the same technique in production environments if these meet your requirements.
JACL is the Java implementation of the popular TCL product. The following example is a proof of concept of producing Java bytecode from non-Java languages and running the resulting bytecode in the database. It does not reflect any Oracle plan to support TCL in the database.
Download JACL/TCLBlend bundle jacl126-noarch.zip from:
http://dev.scriptics.com/software/java/download.html
jacl126-noarch.zip contains:
lib/tcljava.jar 178583 bytes lib/jacl.jar 554467 bytes readme.jacl 147 bytes
In order to simplify the manipulation (loading and
ncomping) of required files in OracleJVM, unJAR/unZIP the contents of all JAR files and re-JAR everything into a single JAR (or ZIP) file.all_jacl.zip 341257 bytes
Grant
javasysprivandjava_deployto your schema (e.g.,scott):SQL>grant javasyspriv, java_deploy to scott;
ncomp -u scott/tiger -load all_jacl.zip
Using a modified variant of StringLengthTest.java from sourceforge.net:
StringLengthTest.java
=============
import tcl.lang.*;
public class StringLengthTest {
public static void main(String[] args) {
int thestr_len = -1;
String thestr = "noggy";
Interp interp = new Interp();
try {
interp.eval("string length "" + thestr + """);
thestr_len = TclInteger.get(interp, interp.getResult());
} catch (TclException ex) {
int code = ex.getCompletionCode();
switch (code) {
case TCL.ERROR:
System.err.println(interp.getResult().toString());
break;
case TCL.BREAK:
System.err.println(
"invoked "break" outside of a loop");
break;
case TCL.CONTINUE:
System.err.println(
"invoked "continue" outside of a loop");
break;
default:
System.err.println(
"command returned bad error code: " + code);
break;
}
} finally {
interp.dispose();
}
System.out.println("string length was " + thestr_len);
}
}Compile
StringLengthTest.javaon the client side, making sure to include all _jacl.zip in your classpath:javac –classpath $PATH:all_jacl.zip:. $*
Load the class file with loadjava:
loadjava u scott/tiger StringLengthTest.class
Run
StringLengthTestin the database (the text is stored inthestrstring):ojvmjava -u scott/tiger@ --OJVMJAVA-- --type "help" at the command line for help message $ java StringLengthTest string length was 5
Variations of
StringLengthTestthat invoke the TCL interpreter (tcl.lang.Interp described in http://www.tcl.tk/man/java1.2.6/TclJavaLib/Eval.htm) are:TclEval,TclRun, andTclResources.TclEval: takes a TCL code as a string and returns a result.TclEval.java ======= import tcl.lang.*; public class TclEval { public static void main(String[] args) { if (args.length != 1) { System.out.println("pass in a tcl script"); } else System.out.println("result: " + doit(args[0])); } public static String doit (String tcl_code) { String result = ""; Interp interp = new Interp(); try { interp.eval(tcl_code); result += interp.getResult(); } catch (TclException ex) { int code = ex.getCompletionCode(); switch (code) { case TCL.ERROR: System.err.println(interp.getResult().toString()); break; case TCL.BREAK: System.err.println( "invoked "break" outside of a loop"); break; case TCL.CONTINUE: System.err.println( "invoked "continue" outside of a loop"); break; default: System.err.println( "command returned bad error code: " + code); break; } } finally { interp.dispose(); } return result; } }As usual, compile and load
TclEval.classin the database. In order to invoke it from SQL, create a PL/SQL wrapper as follows:create or replace function tcl_str (code VARCHAR2) return VARCHAR2 as language java name 'TclEval.doit(java.lang.String) return java.lang.String';
Store the TCL scripts in a table and then evaluate these with
tcl_str:SQL> create table tcl_1 (c VARCHAR2(100), r VARCHAR2(30)); Table created. SQL> insert into tcl_1 values ('string length "xyz"', ''), 1 row created. SQL> select tcl_str(c) from tcl_1; TCL(C) ---------------------------------------------------------- 3A more realistic variant of this would use a CLOB, LOB, or LONG column type.
TclRun.TclRunis similar toStringLengthTest, in that it takes no args and can be invoked fromojvmjava(ojvmjavadoes not understand command-line args).TclRun.java =========== public class TclRun { public static void main(String[] a) { String code = "string length "asdfgh""; String[] args = {code}; TclEval.main(args); } } Compile, load, and play! % ojvmjava -u scott/tiger@ --OJVMJAVA-- --type "help" at the command line for help message $ java TclRun result: 6TclEvalResourceinterprets TCL scripts stored in files or Java resources.
TclEvalResource.java
=============
import tcl.lang.*;
public class TclEvalResource {
public static void main(String[] args) {
if (args.length != 1) {
System.out.println("pass in a tcl script");
}
else
System.out.println("result: " + doit(args[0]));
}
public static String doit (String tcl_resource) {
String result = "";
Interp interp = new Interp();
try {
interp.evalFile(tcl_resource);
result += interp.getResult();
} catch (TclException ex) {
int code = ex.getCompletionCode();
switch (code) {
case TCL.ERROR:
System.err.println(interp.getResult().toString());
break;
case TCL.BREAK:
System.err.println(
"invoked "break" outside of a loop");
break;
case TCL.CONTINUE:
System.err.println(
"invoked "continue" outside of a loop");
break;
default:
System.err.println(
"command returned bad error code: " + code);
break;
}
} finally {
interp.dispose();
}
return result;
}
}TclEvalResource currently reads scripts from files but may be enhanced to read TCL script code from Java resources in the database or from CLOB. Most of these examples are borrowed from http://www.beedub.com/book/2nd/tclintro.doc.html and include simple arithmetic, such as a diagonal function, a factorial function, and callback to java.lang.String. All of these can be evaluated by TclEvalResource.
With a proper Call Spec, TclEvalResource can be invoked from SQL:
SQL> create or replace function tcl (code VARCHAR2)
return VARCHAR2
as language java name
'TclEvalResource.doit(java.lang.String)
return java.lang.String';
2 /
Function created.Try TclEvalResource using t4.tcl, t5.tcl, and t6.tcl scripts:
/tmp/t4.tcl set x 7; set y 9 expr $x + $y SQL> select tcl('/temp/t4.tcl') from dual; TCL('/TEMP/T4.TCL') --------------------------------------------------- 16 /temp/t5.tcl proc Diag {a b} { set c [expr sqrt($a * $a + $b * $b)] return $c } Diag 12 34 SQL<select tcl('/temp/t5.tcl') from dual; TCL('/TEMP/T5.TCL') --------------------------------------------------- 36.0555127546 /temp/t6.tcl proc Factorial {x} { set i 1; set product 1 while {$i <= $x} { set product [expr $product * $i] incr i } return $product } Factorial 10 SQL> select tcl('/temp/t6.tcl') from dual; TCL('/TEMP/T6.TCL') --------------------------------------------------- 3628800
Note that SQL prints only the output from tcl.lang.Interp. All STDOUT output, such as the result of TCL functions puts and putc, as well as all error messages, go to Oracle database trace files (*.trc files), but as we have seen, you can redirect standard output using dbms_java.set_output().
The following example is a foreign proof of concept of hosting the Jython runtime in the database. Once again, this proof of concept does not reflect any Oracle plan to support Jython in the database.
Jython is the Java implementation of the Python runtime (jython.jar) that can run Python scripts. More details on Python or Jython can be easily found on the Web.
The following simple steps will install the Jython runtime in your database:
Download the latest jython implementation from:
Generate
jython.jarby running jython-21:java jython-21
Load
jython.jarin your schema:loadjava -v -u scott/tiger jython.jar
Voilà! You can now run Python scripts in your database!
A basic example:
Fib.py
=====
import java
class Fib(java.lang.Object):
# Fibonacci numbers module
def fib(self, n): # write Fibonacci series up to n
"@sig public void fib(int n)"
a, b = 0, 1
while b < n:
print b,
a, b = b, a+bPython is format sensitive; therefore, if you do not indent, your program will not compile. Note the location where you generate jython.jar; it should also contain
jythonc, the Jython-to-Java bytecode compiler. Use the following command to generate Java source and Java class fromFib.py:jythonc Fib.py
This will create a directory, jpywork, containing the following files:
Fib$_PyInner.class 3430 bytes Fib.class 3623 bytes Fib.java 5526 bytes
jythonctranslates all Jython methods into instance methods; however, PL/SQL can only wrappublic static java method. We need to modifyFib.javaand turn instance methodsFib()into public static onesfib_wrapper(), as follows:public static void fib_wrapper(int n) { new Fib().fib(n); }Compile the new
Fib.javawith a Java compiler (needjython.jarin your classpath).Load the new Fib.class into
scott/tigeras previously.Write a PL/SQL wrapper to expose this to SQL and client-side JDBC:
create or replace procedure fib(n NUMBER) as language java name 'Fib.fib_wrapper(int)'; /
Run Fib from SQL:
SQL> call fib(10) *** 2003-07-17 13:07:35.448 1 1 2 3 5 8
Scheme belongs to the family of Lisp languages; it is also a great vehicle for implementing other languages. For example, the GNU implementation of XQuery (QEXO) is based on Kawa,[6] which is the Java-based Scheme system. Similarly, Kawa is being used to port Perl to Java. Once again, the following example is a proof of concept and does not reflect any plan to support Scheme runtime in the Oracle database.
Get
kawa-1.7.jarfrom http://www.gnu.org/software/kawa and put it in a directory on your file system.Load
kawa-1.7.jarinto the database with the following command:loadjava -v -u scott/tiger kawa-1.7.jar
Voilà! You have a Scheme runtime in your database.!
prime.scm
========
;
; primes
; By Ozan Yigit
;
(define (interval-list m n)
(if (> m n)
'()
(cons m (interval-list (+ 1 m) n))))
(define (sieve l)
(define (remove-multiples n l)
(if (null? l)
'()
(if (= (modulo (car l) n) 0) ; division test
(remove-multiples n (cdr l))
(cons (car l)
(remove-multiples n (cdr l))))))
(if (null? l)
'()
(cons (car l)
(sieve (remove-multiples (car l) (cdr
l))))))
(define (primes<= n)
(display (sieve (interval-list 2 n))))
(primes<= 300)Store the program as
prime.scmon your file system.Compile the Scheme program
prime.scminto a Java class with the following command:java -cp .:/path/to/kawa-1.7.jar kawa.repl --main -C / path/to/prime.scm
This will generate a
prime.classfile in the current directory.Load the
prime.classfile into the database with the following command:loadjava -v -r -u scott/tiger prime.class
Write a PL/SQL wrapper as follows:
SQL> create or replace procedure primnumb(dummy VARCHAR2) 2 as language java name 3 'prime.main(java.lang.String[])'; 4 / SQL>show errors;
Execute:
SQL>set serveroutput on SQL>call dbms_java.set_output(50000); SQL> call primnumb('test'), Call completed.You will see a printout of prime numbers on your screen (or in the corresponding session trace file).
(2 3 5 7 11 13 17 19 23 29 31 37 41 43 47 53 59 61 67 71 73 79 83 89 97 101 103 107 109 113 127 131 137 139 149 151 157 163 167 173 179 181 191 193 197 199 211 223 227 229 233 239 241 251 257 263 269 271 277 281 283 293)
The following example is a foreign proof of concept of hosting the Groovy runtime in OracleJVM. Once again, it does not reflect any Oracle plan to support Groovy in the database.
Groovy is a new scripting language candidate (JSR 241) that is derived from Java. In summary, it is a compact-syntax, dynamic object-oriented scripting language that leverages the Java language and its bytecode. Unlike Jython, JRuby, and other non-Java scripting languages, Groovy does not mix foreign language libraries or classes. However, you can mix pure Java with Groovy scripts and inherit from or use any Java code. More details on Groovy can be found at http://groovy.codehaus.org.
This section contains detailed examples of Groovy scripts running in OracleJVM, the steps needed to load the Groovy environment in the database, and a discussion of Groovy’s integration with SQL. The Groovy language is not yet ready for enterprise production use, but you can already play with it on stand-alone JDK and in OracleJVM. Groovy 1.0 prerelease 2[7] has been recently made available (by the time of this writing); however, the following proof of concept is based on groovy-1.0-beta-6.
In order to run Groovy scripts in the database, we need to first upload the Groovy runtime jars in the database. The Groovy environment can be downloaded from
http://groovy.codehaus.org.As listed hereafter, there are a large number of JARS and classes in the Groovy lib directory:
groovy-1.0-beta-6/lib/ant-1.6.1.jar groovy-1.0-beta-6/lib/ant-junit-1.6.1.jar groovy-1.0-beta-6/lib/ant-launcher-1.6.1.jar groovy-1.0-beta-6/lib/asm-1.4.1.jar groovy-1.0-beta-6/lib/asm-attrs-1.4.3.jar groovy-1.0-beta-6/lib/asm-util-1.4.3.jar groovy-1.0-beta-6/lib/axion-1.0-M3-dev.jar groovy-1.0-beta-6/lib/bsf-2.3.0-rc1.jar groovy-1.0-beta-6/lib/classworlds-1.0.jar groovy-1.0-beta-6/lib/commons-cli-1.0.jar groovy-1.0-beta-6/lib/commons-collections-3.0-dev2.jar groovy-1.0-beta-6/lib/commons-logging-1.0.3.jar groovy-1.0-beta-6/lib/groovy-1.0-beta-6.jar groovy-1.0-beta-6/lib/junit-3.8.1.jar groovy-1.0-beta-6/lib/mockobjects-core-0.09.jar groovy-1.0-beta-6/lib/mx4j-2.0-beta-1.jar groovy-1.0-beta-6/lib/nekohtml-0.7.7.jar groovy-1.0-beta-6/lib/openejb-loader-0.9.2.jar groovy-1.0-beta-6/lib/qdox-1.3.jar groovy-1.0-beta-6/lib/radeox-0.9.jar groovy-1.0-beta-6/lib/radeox-oro-0.9.jar groovy-1.0-beta-6/lib/regexp-1.2.jar groovy-1.0-beta-6/lib/servletapi-2.3.jar groovy-1.0-beta-6/lib/xerces-2.4.0.jar groovy-1.0-beta-6/lib/xml-apis-1.0.b2.jarWhen you are unzipping all JARS, the number of classes reach 4,492; on UNIX/Linux it can be done using:
find groovy-1.0-beta-6/lib/ -name *.jar -exec unzip -l {} ; | wc -l => 4492Loading all of these classes one by one will be cumbersome, will consume too many resources, and will create too many unresolved references; besides, you don’t need to load all of the classes in all JARS. To simplify the loading of Groovy in the database and NCOMPing, we’ll repackage the distribution by bundling only the following classes:
groovy.*,org.codehaus.groovy.*, andorg.objectweb.asm.*intoneeded.zip. This will greatly reduce the number of classes;needed.ziponly has 529 classes and yet contains all classes needed to run Groovy in the database.unzip -l tmp/needed.zip | wc –l => 529
In order to meet the storage requirements of the experience, I recommend adding more space to the tablespace that will be used, indicated as follows:
alter tablespace <tblspace> add datafile '$ORACLE_HOME/../oradata/orcl/t_db2.f' size 500M;The runtime could now be loaded using loadjava with the genmissing options to avoid class resolution error messages:
loadjava -force -genmissing -genmissingjar gm-min.jar - resolve -verbose -u scott/tiger needed.zipIn order to cope with deploying the generated DLLs (upon NCOMPing), increase the storage space in the corresponding tablespace:
alter tablespace <tblspace> add datafile '$ORACLE_HOME/../oradata/orcl/t_db3.f' size 500M;NCOMP it using:
ncomp -u scott/tiger needed.zip
This will produce error messages, but the required/critical DLLs will be generated.
The following DLLs will be “installed”:
libjox10_fe59e88f8d_sys_org_codehaus_groovy_control_messages.so libjox10_fe59e88f8d_sys_org_codehaus_groovy_sandbox_markup.so libjox10_fe59e88f8d_sys_groovy_model.so libjox10_fe59e88f8d_sys_org_codehaus_groovy_sandbox_util.so libjox10_fe59e88f8d_sys_org_codehaus_groovy_control.so libjox10_fe59e88f8d_sys_org_codehaus_groovy_wiki.so libjox10_fe59e88f8d_sys_groovy_swing.so libjox10_fe59e88f8d_sys_groovy_security.so libjox10_fe59e88f8d_sys_org_codehaus_groovy_runtime.so libjox10_fe59e88f8d_sys_org_codehaus_groovy_classgen.so libjox10_fe59e88f8d_sys_groovy_xml.so libjox10_fe59e88f8d_sys_org_codehaus_groovy_syntax.so libjox10_fe59e88f8d_sys_org_codehaus_groovy_syntax_parser.so libjox10_fe59e88f8d_sys_org_codehaus_groovy_control_io.so libjox10_fe59e88f8d_sys_org_codehaus_groovy.so libjox10_fe59e88f8d_sys_groovy_xml_dom.so libjox10_fe59e88f8d_sys_groovy_swing_impl.so libjox10_fe59e88f8d_sys_groovy_servlet.so libjox10_fe59e88f8d_sys_org_codehaus_groovy_syntax_lexer.so libjox10_fe59e88f8d_sys_org_codehaus_groovy_ast.so libjox10_fe59e88f8d_sys_org_codehaus_groovy_ant.so libjox10_fe59e88f8d_sys_org_objectweb_asm.so libjox10_fe59e88f8d_sys_groovy_text.so libjox10_fe59e88f8d_sys_groovy_lang.so libjox10_fe59e88f8d_sys_org_codehaus_groovy_ast_expr.so libjox10_fe59e88f8d_sys_groovy_sql.so libjox10_fe59e88f8d_sys_org_codehaus_groovy_tools_xml.so libjox10_fe59e88f8d_sys_org_codehaus_groovy_ast_stmt.so
Next, you need to install a Groovy shell or eval environment, in the database:
/// File TestGroovyShellFileX.java //////// import java.io.*; import groovy.lang.*; public class TestGroovyShellFileX { public static void main (String[] args) { System.out.println("-- starting " + args[0]); try { // call groovy expressions from Java code Binding binding = new Binding(); GroovyShell shell = new GroovyShell(binding); Object value = shell.evaluate(new File(args[0])); } catch (Exception e) { System.out.println("-- got: " + e); } } } $ javac -classpath groovy-1.0-beta-6/lib/groovy-1.0-beta-6.jar TestGroovyShellFileX.java $ loadjava -resolve -verbose -u sys/<path>install TestGroovyShellFileX.class
Now let’s see how you can run Groovy scripts in the database. Groovy scripts (*.gro) can be converted to Java code by means of groovyc, which is available in Groovy installations.
The classes generated by groovyc can be loaded with loadjava and invoked directly in the database, as in the following example:
//// File hellow.gro
3.times { println 'Hello World!'; x = 123; println (x * x); }
$ groovyc --classpath classes12.jar hellow.gro
$ zip hellow.zip hellow*.class
$ loadjava -resolve -verbose -u scott/tiger hellow.zip
$ ojvmjava -u scott/tiger
--OJVMJAVA--
--type "help" at the command line for help message
$ java hellow
Hello World!
15129
Hello World!
15129
Hello World!
15129We can design a Java program that takes a Groovy script as input and transparently converts it into a Java class (instead of running it), upload the resulting class (with related classes), and then let the Java developer instantiate such a class and invoke it. Such operation corresponds to a dynamic invocation of the Groovy script. The following Java code snippet TestDynLoad.java does just that:
/// File TestDynLoad.java ///////////
import java.io.*;
import groovy.lang.*;
public class TestDynLoad {
public static void main (String[] args) {
System.out.println("-- loading " + args[0]);
try {
GroovyClassLoader loader = new GroovyClassLoader();
Class groovyClass = loader.parseClass( new File(args[0]) );
GroovyObject groovyObject = (GroovyObject)
groovyClass.newInstance();
Object[] run_args = {};
groovyObject.invokeMethod( "run",run_args );
} catch (Exception e) {
System.out.println("-- got: " + e);
}
}
}
% javac -classpath groovy-1.0-beta-6/lib/groovy-1.0-beta-6.jar
TestDynLoad.java
% loadjava -resolve -verbose -u scott/tiger TestDynLoad.classNow, any Groovy script can be dynamically uploaded as a class and invoked directly in the database using the ojvmjava shell:
% ojvmjava -u scott/tiger --OJVMJAVA-- --type "help" at the command line for help message $ java TestDynLoad hellow.gro -- loading hellow.gro Hello World! 15129 Hello World! 15129 Hello World! 15129
In the JACL proof of concept, we mentioned how TCL scripts can be stored in Oracle database table columns and executed from SQL queries. This is achieved by means of a Java driver class defining a static method that takes the TCL code as a string, evaluates it, and returns the result as a string, and a corresponding PL/SQL wrapper function, which can be called from SQL queries. Similarly, a Java driver and a PL/SQL wrapper can be provided for Groovy, as follows:
First, the
TestGroovyShelldriver suitable for receiving and executing code as text from main’s args; we will also equip it with a static method, which takes code as a string and returns the result of the evaluation as Java string. Here is theTestGroovyShell:/// File TestGroovyShell.java ///////////// import groovy.lang.*; public class TestGroovyShell { public static void main (String[] args) { // System.out.println("-- starting "); Binding binding = new Binding(); if (args.length > 0) { eval(args[0], binding); } else { binding.setVariable("foo", new Integer(2)); Object value = eval("println 'Hello World!'; x = 123; return foo * 10"); System.out.println("-- value: " + value); System.out.println("-- variable x: " + binding.getVariable("x")); } } public static Object eval (String code, Binding binding) { GroovyShell shell = new GroovyShell(binding); Object value = null; try { value = shell.evaluate(code); } catch (Exception e) { System.out.println("-- got: " + e); } return value; } public static Object eval (String code) { Binding binding = new Binding(); return eval(code, binding); } public static String evalAsString (String code) { return eval(code, new Binding()).toString(); } }Compile and load
TestGroovyShell.classin the database:% javac -classpath .:groovy-1.0-beta-6/lib/groovy-1.0-beta- 6.jar TestGroovyShell.java % loadjava -resolve -verbose -u scot/tiger TestGroovyShell.classEvaluate Groovy scriptlets interactively using the
ojvmjavashell:% ojvmjava -u scott/tiger --OJVMJAVA-- --type "help" at the command line for help message $ java TestGroovyShell "println System.currentTimeMillis();" 1096262669077 $ java TestGroovyShell " [1,2,3].each { println it}" 1 2 3 $ java TestGroovyShell " println([1,2,3].findAll { it > 0 })" [1, 2, 3] $ java TestGroovyShell "closure = [1,2,3].min; println(closure());" 1Let’s now create a PL/SQL wrapper and use SQL to store and retrieve Groovy scriptlets in the database:
SQL> create or replace function groovy_eval (code VARCHAR2) return VARCHAR2 as language java name 'TestGroovyShell.evalAsString(java.lang.String) return java.lang.String'; 2 / Function created. SQL> create table groovy_code (code VARCHAR2(200)); Table created. SQL> insert into groovy_code values ('[10,2,3,2,1].min()'), 1 row created. SQL> insert into groovy_code values ('[10,2,3,2,1].max()'), 1 row created. SQL> insert into groovy_code values ('[10,2,3,2,1].reverse()'), 1 row created. SQL> insert into groovy_code values ('[10,2,3,2,1].findAll { it > 2 } '), 1 row created. SQL> insert into groovy_code values ('[10,2,3,2,1].sort()'), 1 row created. SQL> select * from groovy_code; CODE ---------------------------------------------------------- [10,2,3,2,1].min() [10,2,3,2,1].max() [10,2,3,2,1].reverse() [10,2,3,2,1].findAll { it > 2 } [10,2,3,2,1].sort() SQL> select groovy_eval(code) from groovy_code; GROOVY_EVAL(CODE) ------------------------------------------- 1 10 [1, 2, 3, 2, 10] [10, 3] [1, 2, 2, 3, 10] SQL>
Because we are running in the database, it makes sense to make SQL more “Groovy”! Groovy SQL performs queries and SQL statements, passing in variables easily with proper handling of statements, connections, and exception handling using closures.
Let’s look at an example using Groovy SQL along with GroovyMarkup. The following example is a modified version of the SQL access example from the Groovy Web site. It demonstrates both SQL access and XML generation using Groovy running in the schema SCOTT. The script iterates over a result set for the table EMP, composing an XML document on the fly.
sqlrepdb.gro
========
import groovy.sql.Sql
import groovy.xml.MarkupBuilder;
import oracle.jdbc.OracleDriver
driver = new OracleDriver()
con = driver.defaultConnection();
println "con = $con"
sql = new Sql( con )
print "sql = $sql"
xml = new MarkupBuilder()
print xml
xml.xul() {
menubar( id:'MAIN' ) {
menu( label:'Employees' )
sql.eachRow( 'select EMPNO, ENAME from emp' ) { row |
xml.menuitem( id:"${row.EMPNO}", name:"${row.ENAME}" )
}
}
}
println xmlInvocation with TestGroovyShellFileX:
$ ojvmjava -u scott/tiger $ java TestGroovyShellFileX sqlrepdb.gro -- starting sqlrepdb.gro con = $con sql = $sqlgroovy.xml.MarkupBuilder@5e8324c6<xul> <menubar id='MAIN'> <menu label='Employees' /> <menuitem name='SMITH' id='7369' /> <menuitem name='ALLEN' id='7499' /> <menuitem name='WARD' id='7521' /> <menuitem name='JONES' id='7566' /> <menuitem name='MARTIN' id='7654' /> <menuitem name='BLAKE' id='7698' /> <menuitem name='CLARK' id='7782' /> <menuitem name='SCOTT' id='7788' /> <menuitem name='KING' id='7839' /> <menuitem name='TURNER' id='7844' /> <menuitem name='ADAMS' id='7876' /> <menuitem name='JAMES' id='7900' /> <menuitem name='FORD' id='7902' /> <menuitem name='MILLER' id='7934' /> </menubar> </xul>groovy.xml.MarkupBuilder@5e8324c6
Instead of dumping the XML to System.out (i.e., the screen) as a result of using ‘println xml’ at the end of sqlrepdb.gro, you can direct Groovy to write to a file using the following instruction:
// replace println xml with
new File("records.xml").newPrintWriter().println(xml)Alternatively, if the expected/resulting XML text is large, you might instead use the XML builder with a writer in argument, as follows:
// replace println xml with
new MarkupBuilder(new File("records.xml").newPrintWriter());Let’s look at another example using Groovy to convert a SQL Result Set into XML[8] and store it in a file:
/// File xmlgen.gro /////////////////////
import groovy.sql.Sql
import java.io.File
import java.sql.DriverManager
import oracle.jdbc.OracleDriver
def toAttr(n) { s == null ? 'NULL' : "'${s}'" }
driver = new OracleDriver()
con = driver.defaultConnection();
sql = new Sql( con );
new File("ResultSet.xml ").withWriter() { writer |
writer.writeLine ("<employees">");
sql.queryEach("select * from scott.emp") { row |
writer.writeLine (
" <employee>" +
" id=" + toAttr(row.EMPNO) +
" name=" + toAttr(row.ENAME) +
" position=" + toAttr(row.JOB) +
" manager=" + toAttr(row.MGR) +
">"
)
};
writer.writeLine ("</employees">");
}
which would create a file ResultSet.xml containing:
<employees>
<employee id='7369' name='SMITH' position='CLERK' manager='7902'>
<employee id='7499' name='ALLEN' position='SALESMAN'
manager='7698'>
...
</employees>However, OracleJVM security will not allow code to work unless the output file—in this case, ResultSet.xml—is granted java.io.FilePermission.
SQL>dbms_java.grant_permission("SCOTT","
java.io.FilePermission" "ResultSet.xml", "write");In addition, the schema requires java.util.PropertyPermission against the current/target directory.
SQL>dbms_java.grant_permission("SCOTT","
java.util.PropertyPermission" "<current directory>",
"read");Per OracleJVM security best practices, the permissions have been specifically granted for the script file and the current directory.
The script is invoked as:
$ java TestGroovyShellFileX xmlgen.groThe beauty of Groovy lies in the fact that, unlike other scripting languages, it adds high-level mapping to existing Java libraries; in the SQL and XML examples, we have just mapped and used the server-side JDBC.
This chapter completes Part I. Now that you’ve learned everything you ever wanted to know about Java in the database and practical and extreme examples, Part II, III, and IV will look at programming APIs that you can use to build Java applications, which you can run in the database or in the middle tier: JDBC and SQLJ. In addition, we’ll also look at magic JPublisher, the multipurpose tool for accessing the Oracle database.