So in my spare time I decided to write a utility class that would let me capture DBMS_OUTPUT lines in case I wanted to do something like this in the future. This isn't exactly new and has been covered here and on Ask Tom, however I didn't like either of those approaches. I wanted my utility class to work with try-with-resources so that I wouldn't have to write any boring boiler plate code when using this utility.
This is what I came up with...
Java
package net.igorkromin;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class DbmsOutputCapture implements AutoCloseable {
private int captureLines = 1024;
private CallableStatement enableStmt;
private CallableStatement readLineStmt;
private CallableStatement disableStmt;
public DbmsOutputCapture(Connection dbConn) throws SQLException {
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, captureLines);
}
public DbmsOutputCapture(Connection dbConn, int captureLines) throws SQLException {
this(dbConn);
this.captureLines = captureLines;
}
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 == captureLines);
}
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;
}
}
This utility class requires a Connection object to be passed into its constructor. There is an overloaded constructor that also takes an int, specifying the number of lines to read back from the database in one go; this can be useful for performance tuning. The execute() method is used to execute your CallableStatement and returns a list of Strings, one String per line of DBMS_OUTPUT captured.
Output is read back from the database using the GET_LINES Procedure until no more output is available. DBMS_OUTPUT is enabled before userCall is executed, and then disabled after its execution is completed and all output is captured.
Using this utility is simple, create a Connection and your CallableStatement that you want to run and capture DBMS_OUTPUT from. Then create the DbmsOutputCapture class passing in the conn object. Call execute() on the capture object. Because DbmsOutputCapture implements AutoCloseable there is no need to mess around with closing it manually.
Java
try (Connection conn = DriverManager.getConnection(
"jdbc:oracle:thin:@//databasehost:1521/XE", connectionProps);
CallableStatement userCall = conn.prepareCall("begin MY_API.DO_CALL(); end;");
DbmsOutputCapture capture = new DbmsOutputCapture(conn)
)
{
List<String> lines = capture.execute(userCall);
...
}
The capture object is reusable multiple times, so if you have several CallableStatements to execute and capture DBMS_OUTPUT from, you only need to instantiate one copy of DbmsOutputCapture.
-i
