By Jaromir D.B. Nemec
Groovy language has some very nice and interesting features that can be
useful in everyday work with the database. Closures, template and builder
support (e.g. to build XML) and simplified JDBC access are some examples.
Oracle has implemented java for a long time and groovy is a language running in
JVM. Why not enable the Oracle database for scripting with groovy?
The best way to start with is “Hello World”. The groovy script to do so
is the following:
// file
HelloGroovy.groovy
println "Hello
Groovy"
To compile the script and to get an ordinary java bytecode class run
groovyc. Please check the reference section below for links for additional
information about groovy and groovy installation.
groovyc
HelloGroovy.groovy
After copying the compiled class file (HelloGroovy.class) in a folder
reachable from Oracle I run it using the runjava statement. Note that it is
required to have the all-inclusive groovy jar file in the classpath.
SQL> SET SERVEROUTPUT ON;
SQL> CALL dbms_java.set_output(20000);
Call completed.
SQL> select dbms_java.runjava ('-cp
/appl/home/oracle/groovy/groovy-all-1.5.7.jar:/appl/home/oracle/groovy/example
HelloGroovy') as hello from dual;
So far, so good. But ouch!!!
select dbms_java.runjava ('-cp /appl/home/oracle/groovy/groovy-all-1.5.7.jar:/appl/home/oracle/groovy/example
HelloGroovy') as hello from dual
*
ERROR at line 1:
ORA-29532: Java call terminated by uncaught Java
exception:
<cut to save space>
Caused by: java.security.AccessControlException: the Permission
(java.lang.RuntimePermission accessDeclaredMembers) has not been granted
to
ProtectionDomain
(file:/appl/home/oracle/groovy/groovy-all-1.5.7.jar <no signer certificates>)
Well, java security poses some problems. Let’s ignore it for a moment –
we will have to deal with security later. Let’s see if SYS can cope with the
problems.
SQL> connect sys as sysdba
Connected.
SQL> SET SERVEROUTPUT ON;
SQL> CALL dbms_java.set_output(20000);
Call completed.
SQL> select dbms_java.runjava ('-cp
/appl/home/oracle/groovy/groovy-all-1.5.7.jar:/appl/home/oracle/groovy/example
HelloGroovy') as hello from dual;
HELLO
-------------
Hello Groovy
Fine! It works. But (despite the security issue) is this really what we
expect of Groovy? The need to compile the script is the first obstacle.
Additionally, it will be much more comfortable to get the result – the greeting
string - as a result of the select statement. Note that in our case the select
returns NULL; the output is written on the serveroutput.
The Internet did not help us to find a better solution until the magic formula
was used. The problem-solver was the Google search of the following string
(using quotes):
“Running Groovy Scripts in
the Database”
After some initial problems I managed to load the Groovy in the database
using following parameters of the loadjava command. Here is the script to create the database
user.
loadjava -user groovy -genmissing
-grant
public -verbose -resolve groovy-all-1.5.7.jar
Classes Loaded: 1662
Resources Loaded: 52
Sources Loaded: 0
Published Interfaces: 0
Classes generated: 87
Classes skipped: 0
Synonyms Created: 0
Errors: 0
The genmissing option was the “enabler” of the success. Note that in
order to delete all of the thousands of loaded classes the following statement
can be used:
dropjava -user groovy
groovy-all-1.5.7.jar
The status of loaded objects can be verified selecting the DBA_OBJECTS
view.
select object_type, status, count(*)
from dba_objects where owner = 'GROOVY' and object_type like '%JAVA%'
group by object_type, status;
Don’t panic if you see some classes in invalid state. Important is if
the examples below can be run.
The java class TestGroovyShell from the book [1] is used. I have
undertaken minor changes, especially to allow passing parameters. Here is the
adapted source.
The java source TestGroovyShell.java must be compiled and loaded in the
database. The groovy ALL jar must be in classpath.
$ javac -classpath
/appl/home/oracle/groovy/groovy-all-1.5.7.jar TestGroovyShell.java
$ loadjava -resolve -grant public -verbose -u groovy TestGroovyShell.class
In the next step the PL/SQL wrapper for the class TestGroovyShell must be created.
CREATE OR REPLACE FUNCTION groovy_eval(code VARCHAR2)
RETURN VARCHAR2 AS
LANGUAGE java name
'TestGroovyShell.evalAsString(java.lang.String) return java.lang.String';
/
OK, this input is sufficient to be able to carry out a test.
SQL> select groovy_eval('return "Hello
Groovy!"') from dual;
GROOVY_EVAL('RETURN"HELLOGROOVY!"')
---------------------------------------------------------------------------
Hello Groovy!
Excellent! Let’s try something less trivial:
SQL> select groovy_eval('System.properties') as
groovy from dual;
-- starting System.properties
-- security manager used: oracle.aurora.rdbms.SecurityManagerImpl
-- got: java.security.AccessControlException: the
Permission (groovy.security.GroovyCodeSourcePermission /groovy/shell) has not
been granted to GROOVY. The PL/SQL to grant this is dbms_java.grant_permission(
'GROOVY', 'GROOVY:groovy.security.GroovyCodeSourcePermission', '/groovy/shell',
'' )
Security Problem! After some research I found two important pieces of
information:
a)
extend
or rewrite the security manager
Combining those pieces of information I created a workaround that
completely deactivates the SecurityManager (which is not recommended and not at
all a clean solution but it works for my test environment). I hope a better
solution will be available soon.
The java class to override the security manager (SetNullSecurityManager)
can be found here.
The PL/SQL wrapper for it must be defined.
CREATE OR REPLACE FUNCTION set_security_manager RETURN
VARCHAR2 AS
LANGUAGE java name
'SetNullSecurityManager.setSecurityManager()
return java.lang.String';
/
One last thing must be done. We need to grant the java privilege to the
database user – GROOVY in my case. The list below is the result of my
experiments.
exec dbms_java.grant_permission( 'GROOVY',
'SYS:java.lang.RuntimePermission', 'setSecurityManager', '' );
exec dbms_java.grant_policy_permission ('GROOVY',
'GROOVY', 'groovy.security.GroovyCodeSourcePermission', '*');
exec dbms_java.grant_permission( 'GROOVY', 'GROOVY:groovy.security.GroovyCodeSourcePermission',
'/groovy/shell', '' );
exec dbms_java.grant_permission( 'GROOVY',
'SYS:java.util.PropertyPermission', '*', 'read,write' );
exec dbms_java.grant_permission( 'GROOVY',
'SYS:java.lang.reflect.ReflectPermission', 'suppressAccessChecks', '' );
Interestingly I didn’t manage to run the examples neither granting
privileges nor with the NullSecurityManager. So this is the mix I used with
success.
Finally, an anonymous PL/SQL block is defined to change the security
manager.
SET SERVEROUTPUT ON
CALL dbms_java.set_output(20000);
declare
rt varchar2(1000);
sm varchar2(1000);
begin
-- reset java session state
rt := dbms_java.endsession_and_related_state;
-- set security manager
sm:= set_security_manager;
dbms_output.put_line('reset state: '|| rt);
dbms_output.put_line('security manager: '|| sm);
end;
/
After some experimenting I found that the function endsession_and_related_state should be called along with the setting of the
new security manager. Otherwise the security exceptions prevail. This may be
specific for 11g.
Now we can try a template example.
select groovy_eval('import
groovy.text.SimpleTemplateEngine as STE
def simpleTemplate = new STE().createTemplate(''today is
${day}:${month}'')
simpleTemplate.make(["day":"08",
"month":"02"]).toString()') from dual;
today is 08:02
A Groovy template is a very simple and effective way to substitute
parameters (binding) in a predefined text frame. Check the reference section for
further information on Groovy templates.
The next example combines the database access and the Groovy XML
builder.
select groovy_eval(
'import groovy.sql.Sql
import groovy.xml.MarkupBuilder
import oracle.jdbc.OracleDriver
driver = new OracleDriver()
con = driver.defaultConnection()
def sql = new Sql(con)
def writer = new StringWriter()
def builder = new MarkupBuilder(writer)
def xml = builder.elements {
sql.eachRow
("select rownum id, ''xxxx''||rownum des from dual connect by level
< 5")
{
row
-> element (id:row.id, description:row.des)
}
}
writer.toString()') as res from dual;
<elements>
<element
id='1' description='xxxx1' />
<element
id='2' description='xxxx2' />
<element id='3'
description='xxxx3' />
<element
id='4' description='xxxx4' />
</elements>
Groovy builders are not limited to XML. Builder supports html as well.
The above example illustrates also the internal connection to the database using
the Groovy wrapped JDBC.
The final example combines all the presented features. A Groovy script
stored in XDB is loaded and executed with parameter passed. The scripts produce
a statement to gather optimiser statistics for a specific table. The table name
to consider and the required options are passed as parameters.
The key part of the script is the template for the gather_table_stats
statement. In a cleaned form the template is shown below. Note that in the
script some escaping must be performed.
exec
dbms_stats.gather_table_stats(ownname=>user,tabname=>'${tableName}',method_opt=>'FOR
ALL COLUMNS SIZE ${colSize}', estimate_percent=>${estPercent},cascade =>
TRUE, stattab=>'mystats',statid=>'${tableName}_${colSize}_${estPercent}',statown=>'GROOVY'
);
This is just a normal statement; only the values for tabname, column
size and estimated percent are defined as variables.
We must define a wrapper function to be able to call the script.
CREATE OR REPLACE FUNCTION
groovy_interpret(script_name VARCHAR2, params VARCHAR2)
RETURN VARCHAR2 AS
--
script CLOB;
BEGIN
-- get the script from XDB
script :=
XDBURIType(script_name).getCLOB();
-- and run it
return groovy_eval_with_params(cast(script
as VARCHAR2),params);
END;
/
OK, let’s go!
select groovy_interpret(
'/public/test/groovy/genGatherTableStats.groovy',
'tableName:''groovy_code'',colSize:''AUTO'',
estPercent:''50''') as stmt from dual;
exec dbms_stats.gather_table_stats(ownname=>user,tabname=>'groovy_code',method_opt=>'FOR
ALL COLUMNS SIZE AUTO', estimate_percent=>50,cascade => TRUE,
stattab=>'mystats',statid=>'groovy_code_AUTO_50',statown=>'GROOVY' );
This call simple substitutes the three parameters. But that’s not all we
can do. We want to test the histogram with different column sizes: 10,20 and
AUTO. Note that the statid uniquely defines the result in the stat_table, so we
can run the result script and check all the gathered statistics in the stat_table.
The key feature here is that is we pass a collection as a value of a parameters
all combinations are examined.
select
groovy_interpret('/public/test/groovy/genGatherTableStats.groovy',
'tableName:''groovy_code'',colSize:[''AUTO'',10,20],
estPercent:''50''') as stmt from dual;
exec
dbms_stats.gather_table_stats(ownname=>user,tabname=>'groovy_code',method_opt=>'FOR
ALL COLUMNS SIZE AUTO', estimate_percent=>50,cascade => TRUE,
stattab=>'mystats',statid=>'groovy_code_AUTO_50',statown=>'GROOVY' );
exec
dbms_stats.gather_table_stats(ownname=>user,tabname=>'groovy_code',method_opt=>'FOR
ALL COLUMNS SIZE 10', estimate_percent=>50,cascade => TRUE,
stattab=>'mystats',statid=>'groovy_code_10_50',statown=>'GROOVY' );
exec dbms_stats.gather_table_stats(ownname=>user,tabname=>'groovy_code',method_opt=>'FOR
ALL COLUMNS SIZE 20', estimate_percent=>50,cascade => TRUE,
stattab=>'mystats',statid=>'groovy_code_20_50',statown=>'GROOVY' );
What about testing different sample sizes? No problem.
select groovy_interpret('/public/test/groovy/genGatherTableStats.groovy',
'tableName:''groovy_code'',colSize:''AUTO'',
estPercent:(1..3)') as stmt from dual;
exec
dbms_stats.gather_table_stats(ownname=>user,tabname=>'groovy_code',method_opt=>'FOR
ALL COLUMNS SIZE AUTO', estimate_percent=>1,cascade => TRUE,
stattab=>'mystats',statid=>'groovy_code_AUTO_1',statown=>'GROOVY' );
exec
dbms_stats.gather_table_stats(ownname=>user,tabname=>'groovy_code',method_opt=>'FOR
ALL COLUMNS SIZE AUTO', estimate_percent=>2,cascade => TRUE,
stattab=>'mystats',statid=>'groovy_code_AUTO_2',statown=>'GROOVY' );
exec
dbms_stats.gather_table_stats(ownname=>user,tabname=>'groovy_code',method_opt=>'FOR
ALL COLUMNS SIZE AUTO', estimate_percent=>3,cascade => TRUE,
stattab=>'mystats',statid=>'groovy_code_AUTO_3',statown=>'GROOVY' );
But there is no need to test on such detailed level. Let try go through
in steps of five. (The current solution has a limitation that the result must
match the VARCHAR2 type).
select groovy_interpret('/public/test/groovy/genGatherTableStats.groovy',
'tableName:''groovy_code'',colSize:''AUTO'',estPercent:((1..3).collect
{it*5})') as stmt from dual;
/*
exec
dbms_stats.gather_table_stats(ownname=>user,tabname=>'groovy_code',method_opt=>'FOR
ALL COLUMNS SIZE AUTO', estimate_percent=>5,cascade => TRUE,
stattab=>'mystats',statid=>'groovy_code_AUTO_5',statown=>'GROOVY' );
exec
dbms_stats.gather_table_stats(ownname=>user,tabname=>'groovy_code',method_opt=>'FOR
ALL COLUMNS SIZE AUTO', estimate_percent=>10,cascade => TRUE,
stattab=>'mystats',statid=>'groovy_code_AUTO_10',statown=>'GROOVY' );
exec
dbms_stats.gather_table_stats(ownname=>user,tabname=>'groovy_code',method_opt=>'FOR
ALL COLUMNS SIZE AUTO', estimate_percent=>15,cascade => TRUE,
stattab=>'mystats',statid=>'groovy_code_AUTO_15',statown=>'GROOVY' );
There are still some obstacles as the handling of the SecurityManager.
Additional support is required to be able to use it effectively (such a
returning a CLOB type). But, yes, Oracle can run Groovy!
[1] Oracle
Database Programming Using Java and Web Services by Kuassi Mensah
[2] Groovy
[4] Groovy
Templates
[5] Groovy Builders
[6] Groovy SQL
A GUSS is a search string that passed to the Google search returns exactly one reference. The role of GUSS is similar to the role of database primary keys.
At the time of writing, an example of such a GUSS was "running
groovy scripts in the database"; returning only one hit pointing to
Mensah’s book. Note that if you found this article using Google search, the
mentioned string will have lost the GUSS attribute for obvious reasons.
Interestingly enough, at the time of writing even the term “Google
Unique Search String” was a GUSS.
Last revision
22.2.2009
Jaromir D.B. Nemec is a freelancer specializing in data warehouse and integration solutions. He can be reached at http://www.db-nemec.com