4

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.

2 Answers 2

2

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.

3
  • Interesting... do you have a reference for Oracle using Java for the add-ons? If Oracle is using the JVM internally then that really shoots holes in any kinds of "well, no one uses it and it's likely to go away" arguments.
    – Scott A
    Jul 21, 2011 at 18:17
  • 1
    Directly, no, but how about from AskTom? You Asked I've written a program which uses the utl_smtp to send a email to myself. However, when I run the procedure, the following error occurs: ORA-29540: class oracle/plsql/net/TCPConnection does not exist and we said... Some of the installer scripts forget to load the needed jar file for TCP/IP support in PLSQL. Simply: cd plsql/jlib loadjava -user sys/password plsql.jar
    – mezmo
    Jul 21, 2011 at 20:44
  • That's a start, thanks, although it's from 2001. I have found indications that Oracle Apps uses JServer too.
    – Scott A
    Jul 22, 2011 at 1:37
0

Are stored procedures a good idea?

Stored procedures add considerable significant CPU overhead, and Oracle databases tend to be very greedy on CPU. If you run out of CPU, you're hosed. Therefore I strongly suggest developing realistic benchmarks to see whether you're potentially going to put your database in trouble.

Assuming that the overhead of running the stored procedure won't be a problem, I would not hesitate to use Java for this. Oracle first implemented Java stored procedures in Oracle 8i (8.1.5). That was released in February 1999, so the feature has been out for a dozen years and has had several major releases to get bugs fixed.

2
  • 3
    Hey Ben, long time... :-) Stored procedures aren't the question. We have probably 1M LoC of PL/SQL right now. The question is will we see any issues with the JVM, jit, garbage collection (even though the Java shim is tiny and written so as to avoid gc as much as possible), or other things like unpublished bugs.
    – Scott A
    Jul 21, 2011 at 13:37
  • 1
    bad answer. Stored procedures add overhead in the database, performing the same functionality outside the database may well (and often does) add even more overhead.
    – jwenting
    Jun 26, 2013 at 11:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.