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
