I'm considering using a Java stored procedure as a very small shim to allow UDP communication from a PL/SQL package. Oracle does not provide a UTL_UDP to match its UTL_TCP. There is a 3rd party XUTL_UDP that uses Java, but it's closed source (meaning I can't see how it's implemented, not that I don't want to use closed source).
An important distinction between PL/SQL and Java stored procedures with regards to networking: PL/SQL sockets are closed when dbms_session.reset_package is called, but Java sockets are not. So if you want to keep a socket open to avoid the tear-down/reconnect costs, you can't do it in sessions that are using reset_package (like mod_plsql or mod_owa HTTP requests).
I haven't used Java stored procedures in a production capacity in Oracle before. This is a very large, heavily-used database, and this particular shim would be heavily used as well (it serves as a UDP bridge between a PL/SQL RFC 5424 syslog client and the local rsyslog daemon).
Am I opening myself up for woe and horror, or are Java stored procedures stable and robust enough for usage in 10g? I'm wondering about issues with the embedded JVM, the jit, garbage collection, or other things that might impact a heavily used database.
Best Answer
One of the main ways Oracle has managed to build all the new PL\SQL add-ons they've introduced over the years is via the embedded JVM, so I think that's less of an issue. I've used them in the past and found them no worst than PL\SQL procs, so you wouldn't necessarily want it to do something that would be running all the time, but a quick operation and then done is fine. Some issues are, the JVM is often a generation or 2 behind the current release, not sure about this in recent history though with Oracle being the source of both the DB and Java. The fact that you have to wrap your functionality in a static method, then wrap that in a PL\SQL block is kind of a hassle. Other than "religious issues" I see no other real problem with using them at all.