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.

Base64 decode using PL/SQL

Oracle 10g

For those of us that deal with internet based communication, Base64 encode/decode is a must. It is a method for us to move binary data files over a text based communication channel.

In my environment, I receive files over HTTP via the MOD_PLSQL gateway. MOD_PLSQL implements the bare necessities in terms of HTTP communication, so it comes as no surprise that it does not decode uploaded files if those files arrive as GZIP or Base64 encoded. If I think about it, it shouldn't decode them automatically, but at the very least it should record the encoding of the file for later use.

Fortunately, I just happen to know that the 3rd party sends the file encoded using Base64. Only thing is, the PL/SQL package for decoding Base64 strings UTL_ENCODE only decodes RAW datatypes which limits us to 32767 characters. For Base64 strings larger than 32767 characters in size, we needed to role our own solution. So here is my version of decode to support decoding clob to blob.

Extending the simple MS Excel procedure to include headers

Oracle 10g

MS Excel

In my previous post titled "Simplest example of writing to MS Excel using an Oracle Stored Procedure", I demonstrated what I believe to be the simplest way of outputting a file that can be used by MS Excel. But the example does not include the column headings.... Okay, thats simple enough. In this post, I will extend on the previous example to include the headings.  So without further ado....

Simplest example of writing to MS Excel using an Oracle Stored Procedure

Oracle 10g
MS Excel

First of all, we need to define exactly what is meant by "writing to Excel worksheet". Most people just mean produce something that MS Excel can show as a worksheet. A CSV file is the simplest file that can be displayed by MS Excel and since it is a simple text markup, is the easiest to produce via PL/SQL.

Generating the file using a stored procedure is seriously simple. But you will need to also give thought to how you will make the file available to excel (how to distribute the file). In my example, I make the file available on a network share. The user will find the file available there and open it using MS Excel.