monline

I strongly discourage everyone from running Java in the JVM of Oracle 9i databases. I had two serious issues with it in a production environment (btw. fully patched to the latest patchset) and would never suggest to use Java in an Oracle PL/SQL session ever again.

1. One of the issues: I had several occasions where an ALTER SYSTEM KILL SESSION '<sid>,<serial>' IMMEDIATE would put the session into the famous "session marked for kill" state if the session was executing some Java stored procedure while the DB sent the kill signal (exception).
2. The other issue was that the PL/SQL session executed a Java stored procedure to authenticate to some LDAP server. (I know that there's a dbms_ldap for this purpose, but the client's DB hosting department refused to install that package and insisted on using some Java code for authentication - it's some sort of company policy for them.) I don't know why, but some of these sessions (I've seen two of them) stuck while executing the Java code. The LDAP authentication uses JNI (what else), which of course relies on TCP sockets. If the network connection between the Oracle DB's Java code and the LDAP server was broken, then there should have been some sort of socket timeout (at least the default OS-level timeout). The Java code should have not been running indefinitely ... which was exactly what it has done. I've found that the two stuck sessions were already running for several months!

I'm sure that the JVM implementation inside Oracle 9i (R2) is quite full of bugs and I'd never use it again if I wouldn't absolutely have to (like in the case of the above LDAP example, where the client insisted on strictly Java based authentication ... even in a PL/SQL application). Btw. it is fully possible that dbms_ldap uses Java at the lower level too.