I wrote a lot of stored procs in pl/sql in my past working-life (before becoming a DBA ) … entire web sites. I wrote some Java code too, but I never mixed the two things in a non-canonical way (what I mean with canonical way is:  Java classes connects to DB and calls SQL or PL/SQL ) .

As a DBA I recently tackled the task to integrate Java stored proc into an Oracle instance … even if IMHO this is not a practice that should be abused ( pl/sql is more performing and equally powerful ) it is possible since the times when Oracle started to flatter Java ( ver. 8.1.5 , 1999) and there are cases when you simply have to do it  (e.g.  you need to integrate Filenet Java libraries into existing pl/sql code on a 2-tier  configured system ) .


So I did it… it took me some time but I did it … and I faced some difficulties and learned 4 lessons that  I want to share here:

  1.  You need to use “loadjava”  utility  to load the Java classes into the DB before using it … and you might need to resize SYSTEM tablespace and JAVA_POOL_SIZE.   Be very careful with the loadjava options : -resolve -verbose -genmissing  are useful   -force might be indispensable in case you have previously loaded classes to replace. ( see support DOC ID 156439.1 )
  2. You need to compile the Java classes for the VM version of the DB they will run on ( e.g. if you work on Oracle 10 then you need Java 1.4 )
  3.  If you want to create a pl/sql wrapper to a Java method this method must be a static function, otherwise you will get:      ORA-29531: no method MY_METHOD_EXISTS_YFB in class MY_CLASS_THAT_HAS_IT     … at runtime …  So you might need to code a top level class or wrapper with a static method.
  4. Logging and other authorizations : Oracle introduces a layer of security for Oracle Database Java Applications in order to grant to Oracle users the access to system resources… you can grant the permissions with dbms_java.grant_permission.  I learned from this blog (thanks Marcelo Ochoa) ,  that in order to use  java.util.logging package you need to bms_java.grant_permission( ‘SCOTT’, ‘SYS:java.util.logging.LoggingPermission’, ‘control’, ” );  other permissions you might need are: dbms_java.grant_permission(‘SCOTT’, ‘java.io.FilePermission’, ‘/oracle/testfolder/myfile’, ‘read, write’);
    dbms_java.grant_permission(‘SCOTT’, ‘java.util.PropertyPermission’,’*’,’read,write’);
    dbms_java.grant_permission(‘SCOTT’, ‘SYS:java.lang.RuntimePermission’, ‘getClassLoader’, ” );

I hope someone will find this post useful and it will help to save some time.