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

Recently I've been working with a PL/SQL package that used DBMS_OUTPUT to generate a report. This report was then copy/pasted into a small Java utility I wrote, which parsed it and generated a visual representation of the report data. Because this didn't need to be automated, I was calling the package manually using SQL Developer. Now that work is over, I started thinking if the Java utility could have been enhanced to capture the report itself.

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

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...