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.

The following code demonstrates this:
  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.


Leon King said...

I thought I'd ask this as I've been unable to find an answer on the web.

I'm using SQLDeveloper 3.0.04. I've no problem getting this to work in an SQLPLUS command line environment, but in the GUI, nada. Additionally, using PL/SQL I get output to the Dbms_Output window. Just not from a compiled java function that works fine in other respects.

Any thoughts?

Leon King.

dayneo said...

Unfortunately I don't use SQLDeveloper as my IDE so I wouldn't really know why it does not give you output from the Java Stored Proc. But I can make an educated guess...

The IDE most likely runs DBMS_OUTPUT.ENABLE automatically before running your stored proc. That's why your DBMS_OUTPUT.PUT* statements give you output to the DBMS_OUTPUT window.

I am not sure how you are executing the Java Stored Procedure, but I would assume that the IDE does not execute the DBMS_JAVA.SET_OUTPUT before running the stored proc.

You could ensure that the DBMS_JAVA.SET_OUTPUT is always set by using an AFTER LOGON trigger that sets it when you logon to the database. All subsequent executes will use the value.

Let us know if this works...

dayneo said...

It is also worth noting that this approach will also output Java exception stacks with no need for additional coding. Here's an example of today's debug run of a Java Stored Procedure that was throwing an exception:

npas@NPASP> set serveroutput on size 1000000
npas@NPASP> call dbms_java.set_output(1000000);

Call completed.

npas@NPASP> begin
4 end;
5 /
Host=; username=npasrec
Default store obtained...
javax.mail.AuthenticationFailedException: EOF on socket
at com.sun.mail.pop3.POP3Store.protocolConnect(
at javax.mail.Service.connect(
at javax.mail.Service.connect(
at org.woodsta.db.javamail.pop.POP.retrieveMails(POP:91)
ERROR at line 1:
ORA-29532: Java call terminated by uncaught Java exception:
javax.mail.AuthenticationFailedException: EOF on socket
ORA-06512: at "NPAS.MAIL_UTILITY", line 23
ORA-06512: at "NPAS.NPA_CORRESPOND", line 10
ORA-06512: at line 3

Anonymous said...

Thank you sir.

Helped us pinpoint the exact error when we had to change our pop email to use ssl.