Monday, November 5, 2012

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.