Igor Kromin |   Consultant. Coder. Blogger. Tinkerer. Gamer.

Some of the work I've been doing not so long ago has been deeply rooted in PL/SQL which was invoked from a Java client. The PL/SQL made heavy use of DBMS_OUTPUT to generate text data that could be parsed by the Java client, which in turn then generated graphs (as images). I needed an easy way of capturing this output and unfortunately when I looked around all I could find were outdated approaches that didn't make use of Java features like AutoCloseable resources, and then there were some examples that plain didn't work.

So I decided to build my own...
 DbmsOutputCapture.java
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class DbmsOutputCapture implements AutoCloseable {
private static final int DEFAULT_LINE_BUF_SZ = 1024;
private int lineBufferSize;
private CallableStatement enableStmt;
private CallableStatement readLineStmt;
private CallableStatement disableStmt;
public DbmsOutputCapture(Connection dbConn)
throws SQLException
{
this(dbConn, DEFAULT_LINE_BUF_SZ);
}
public DbmsOutputCapture(Connection dbConn, int lineBufferSize)
throws SQLException
{
this.lineBufferSize = lineBufferSize;
enableStmt = dbConn.prepareCall("begin dbms_output.enable(NULL); end;");
disableStmt = dbConn.prepareCall("begin dbms_output.disable(); end;");
readLineStmt = dbConn.prepareCall("begin dbms_output.get_lines(?, ?); end;");
readLineStmt.registerOutParameter(1, Types.ARRAY,"DBMSOUTPUT_LINESARRAY");
readLineStmt.registerOutParameter(2, Types.INTEGER,"INTEGER");
readLineStmt.setInt(2, lineBufferSize);
}
public List<String> execute(CallableStatement userCall)
throws SQLException
{
List<String> retLines = new ArrayList<>();
try {
enableStmt.executeUpdate();
userCall.execute();
int fetchedLines;
do {
readLineStmt.execute();
fetchedLines = readLineStmt.getInt(2);
Array array = null;
try {
array = readLineStmt.getArray(1);
String[] lines = (String[]) array.getArray();
/* loop over number of returned lines, not array size */
for (int i = 0; i < fetchedLines; i++) {
String line = lines[i];
retLines.add(line != null ? line : "");
}
}
finally {
if (array != null) {
array.free();
}
}
} while(fetchedLines == lineBufferSize);
}
finally {
disableStmt.execute();
}
return retLines;
}
@Override
public void close()
throws SQLException
{
if (!quietClose(enableStmt, readLineStmt, disableStmt)) {
throw new SQLException("Could not close all callable statements");
}
}
private boolean quietClose(CallableStatement ... callableStatements) {
boolean allSuccess = true;
for (CallableStatement stmt : callableStatements) {
try {
stmt.close();
}
catch (SQLException e) {
allSuccess = false;
}
}
return allSuccess;
}
}




So lets see what this class does. First we need 3x of CallableStatements to hold the statements for enabling, disabling and capturing DBMS_OUTPUT. Then there's a variable to keep track of how many lines we fetch from the database at one time, and a default value for this variable.

The two constructors are used to prepare all of the callable statements via the passed in Connection, with one of the constructors allowing us to overwrite the default number of lines fetched.

The brains of this code is in the execute() method. This method accepts a CallableStatement that we wish to capture DBMS_OUTPUT for. To capture output we first run the statement to enable it, then execute the passed in statement, loop over the DBMS_OUTPUT lines, run the disable statement and return the results as a List<String>.

The documentation for GET_LINES states... "After retrieving the specified number of lines, the procedure returns the number of lines actually retrieved. If this number is less than the number of lines requested, then there are no more lines in the buffer." So this is where the do..while loop comes in. We simply try to fetch the maximum number of lines (as configured in the constructors) until we fetch less than that number. The inner for loop is used to copy returned lines in the current batch to the list of all lines with nulls changed to empty strings.

The close() method simply closes all of the internally used CallableStatement objects and returns.

This is how you use this class...
 Java
try (DbmsOutputCapture capture = new DbmsOutputCapture(conn)) {
List<String> lines = capture.execute(call);
}


It's nice and simple, just instantiate it with a Connection and then run execute(). This mimics what the Connection class would do anyway so it's a simple drop-in replacement. Since this class implements AutoCloseable, you can use it in a try-with-resources statement too.

-i

A quick disclaimer...

Although I put in a great effort into researching all the topics I cover, mistakes can happen. Use of any information from my blog posts should be at own risk and I do not hold any liability towards any information misuse or damages caused by following any of my posts.

All content and opinions expressed on this Blog are my own and do not represent the opinions of my employer (Oracle). Use of any information contained in this blog post/article is subject to this disclaimer.
Hi! You can search my blog here ⤵
NOTE: (2022) This Blog is no longer maintained and I will not be answering any emails or comments.

I am now focusing on Atari Gamer.