Fun with Java Stored Procedures

We support customers on just about every version of EBS from 11.0 (yes, that’s eleven dot oh not 11i10) to the latest R12 RUP. This also means that they also run various versions of the Oracle database as well. The challenge that we run into is that we only want to maintain one codebase for all customers, so we have to be extremely sensitive when writing new code to ensure that it will run on all versions of the Oracle products that we support. That’s no small feat! In fact we have 15+ Oracle EBS instances running in house to test our code against various versions.

Java Stored Procedures are very powerful, but I have certainly run into a number of challenges based on Oracle version dependencies. I thought that I would document them here for others to benefit from.

Issue 1: JVM versions
The Oracle database maintains it’s own version of the JVM that is compliant with a certain version of the JDK. When writing Java code, you must be mindful of the lowest version of the JDK that a customer may have in order to guarantee that the Java code you ship will actually compile! Keep in mind that this depends on the database version and whether or not a customer has upgraded their Oracle JVM. To determine the version of the JDK used by the Oracle JVM check out note 131872.1.

Issue 2: Getting the Java into the Database
Now that you have a baseline as far as writing Java code, how do you write a generic process to create the Java objects within the database that will work for everyone!? Well, here are all the options that I have experimented with, why I decided that some would not work for my situation and what I eventually ended up using.

• loadjava – This is an Oracle provided tool that takes as input a Java source file and uploads it to your schema as a Java object. This obviously is the perfect tool for the job…however… what I found was that for different versions of EBS, the tool was in a different directory and required the execution of a specific environment file to setup environment variables correctly. So, instead of putting a bunch of if statements per version in my installation process, I moved on to the next option.

• DBMS_JAVA.loadjava() Similar to the standalone tool loadjava, this is a standard PL/SQL package that allows you to reference a Java source as a parameter and have it upload the information into the database to create a Java object. This works great, the only issue I ran into was that it must be run on the Oracle Database tier. Because our software is flexible enough to run on any tier or outside of any server associated with EBS, I did not want to limit or require customers to install on a specific server. So… option 2 was out.

• CREATE JAVA command run from SQLPLUS – CREATE JAVA is an Oracle command that allows you to create a Java object in the database. You can essentially wrap your Java code with this statement, run SQLPLUS against the script and you are in business. For example:
  
  
  create or replace and compile java source named "props" as
public class props {
public static void show_props() {
System.getProperties().list(System.out);
}
}
My thought was to create a SQL script and execute it with SQLPLUS to create the Java objects. This worked great on my system… then I tried it with an older version of SQLPLUS… Foiled again… Turns out that there was an issue with the older SQLPLUS parser where it would not allow you execute the SQL. Instead you will get an error that looks similar to this:
  
  
  ERROR at line 1:
ORA-29536: badly formed source: Encountered "" at line 3, column 44.
Was expecting one of:
....
I never took the time to pinpoint the exact version where a fix was put in place, but I know that I can reproduce it at will in SQL*Plus 8.0.6.00 but not 10.1.0.5.0. Another option is put out to pasture.
• CREATE JAVA USING BLOB – The CREATE JAVA command has a number of options for execution. One such invocation is as follows:
  
  
  create or replace and compile java source named "props" using blob select lob from my_table;.So my thought was to create a Java program very similar to loadjava that would connect to my database, load a lob column in my_table with my Java source and then execute this DDL to create the actual object. I created the Java program and got it to load the LOB column with the Java source and things were looking good. Until… I tried it on an older version of the database. Turns out that in 8i databases (Yeah… I know…) when you execute this command you get the following error:
  
  
  ORA-29506: invalid query derived from USING clause
ORA-00936: missing expression
29506. 00000 - "invalid query derived from USING clause"
*Cause: The USING clause did not form a valid query.
*Action: Correct the USING clause.
So anyway, after a forum post regarding this I learned from a very helpful member of the community that in 8i the actual ‘select’ keyword was not necessary. So in reality, this is a valid option! But, by the time I learned about this little issue, I had already moved on to my final option which is what is in use today.
• CREATE JAVA USING ‘key_for_blob’ – Yet another permutation of the CREATE JAVA command that instead of using an explicit SELECT statement, assumes the following:
  
  
  SELECT LOB FROM CREATE$JAVA$LOB$TABLE WHERE NAME = 'key_for_BLOB';So, I created a table in my schema called CREATE$JAVA$LOB$TABLE, used my Java based program I mentioned in the option above to push my Java source code into the LOB column and executed the statement:
  
  
  create or replace and compile java source named "props" using 'my_key';
  
  
  and finally… VICTORY! A solution that I can support across all of my customers’ environments!

So in conclusion to a long winded post, there are a ton of different ways to get Java stored in the database, hopefully my adventure above will help you make the right choice for your needs. If anyone has any other thoughts, methods, etc… definitely leave me a comment.