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

Skip down to comments...
Hope you found this post useful...

...so please read on! I love writing articles that provide beneficial information, tips and examples to my readers. All information on my blog is provided free of charge and I encourage you to share it as you wish. There is a small favour I ask in return however - engage in comments below, provide feedback, and if you see mistakes let me know.

If you want to show additional support and help me pay for web hosting and domain name registration, donations, no matter how small, are always welcome!

Use of any information contained in this blog post/article is subject to this disclaimer.
 
comments powered by Disqus
Other posts you may like...