Simplest way to debug your Oracle Java Stored Procedure

Oracle 9i/10g/11g

Today I came across a situation where I had to debug a Java Stored Procedure. I was under time pressure and just needed to understand where the procedure was failing. 

The simplest way of debugging pretty much any code is the good old print to standard output. And luckily for us, Oracle thought the same way and provided a super simple method of reading from the standard output stream by redirecting the output.

So my solution was easily implemented using the good old System.out.println, placing the print calls strategically throughout the Java procedure. The last little trick that allows you to actually see what was written, is to enable the output in SQL*Plus by turning the server output on and setting the DBMS_JAVA output size.

The following code demonstrates this:

dayneo@RMSD> CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED SimpleSample as
  2  public class SimpleSample
  3  {
  4    public static void simplemsg()
  5    {
  6      System.out.println(new java.util.Date().toString() + ": Hello world!");
  7    }
  8  }
  9  /

Java created.

dayneo@RMSD> create or replace procedure simplesample_says as
  2  language java name 'SimpleSample.simplemsg()';
  3  /

Procedure created.

dayneo@RMSD> set serveroutput on size 1000000
dayneo@RMSD> call dbms_java.set_output(1000000);

Call completed.

dayneo@RMSD> begin
  2     simplesample_says();
  3  end;
  4  /
Mon Nov 05 15:37:03 GMT+02:00 2012: Hello world!

PL/SQL procedure successfully completed.