Java and Oracle - load JAR to Oracle or execute separately?

Question

I have a situation where I need to execute some code that for a number of reasons (that I won't get into here) is better done in Java than in PL/SQL. As I see it there are two options:

1. Create a jar with all my compiled code and other supporting files/other jars, load the jar into Oracle (we're running 10g), and execute the Java from a stored procedure.

   • Pros: The Java code integrates very nicely with the rest of the system, can be called from existing PL/SQL.
   • Cons: I have very little experience with running Java in Oracle.

2. Leave the Java in a separate jar and execute it through shell scripts.

   • Pros: I've written Java like this before so I'm familiar with it.
   • Cons: Poor integration with everything else, will probably require extra manual steps to run and manage.

The Java code will have to read XML data from Oracle tables, and write data (non-XML) to other tables, so the amount of database integration made me think loading the Java code into the database might be a good idea but I'm just not sure...

What experiences do people have loading and running Java code from within Oracle? How easy is it to test and debug? Are there are any special tools required? Any "gotchas" I should be aware of?

Answer 1

I would go with option number 1: loading your java code in your database. I have had good experiences with this approach and you don't need that much experience to get a good solution working with this method.

The only part where embedding Java in your database gets complicated (you'll have to set special permissions for your Java code) is when you need access to external resources (network, file i/o to name a few) and it is clearly not your situation in this problem.