When working with Oracle stored procedures, it is not uncommon to have debug log information available from
DBMS_OUTPUT
commands. For instance, if we have a procedure like this:
CREATE TABLE my_table (i INT);
CREATE OR REPLACE PROCEDURE my_procedure (i1 INT, i2 INT) IS
BEGIN
INSERT INTO my_table
SELECT i1 FROM dual UNION ALL
SELECT i2 FROM dual;
dbms_output.put_line(sql%rowcount || ' rows inserted');
END my_procedure;
/
The procedure works just the same, regardless if we’re reading the output from the
DBMS_OUTPUT
call. It is there purely for logging purposes. Now, if we call the above procedure from a tool like SQL Developer or sqlplus, we could write:
SET SERVEROUTPUT ON
BEGIN
my_procedure(1, 2);
END;
/
To get a result like this:
PL/SQL-Prozedur erfolgreich abgeschlossen.
2 rows inserted
(pardon my german)
How to get this output from JDBC
By default, we don’t get such output from JDBC as the overhead of transferring all this output is usually not worth the trouble. If we still wanted to call the procedure AND get the server output, we cannot simply write
SET SERVEROUTPUT ON
, as that is a command specific to sqlplus. We have to wrap our procedure calls in two other calls:
try (Connection c = DriverManager.getConnection(url, properties);
Statement s = c.createStatement()) {
try {
// First, we have to enable the DBMS_OUTPUT. Otherwise,
// all calls to DBMS_OUTPUT made on our connection won't
// have any effect.
s.executeUpdate("begin dbms_output.enable(); end;");
// Now, this is the actually interesting procedure call
s.executeUpdate("begin my_procedure(1, 2); end;");
// After we're done with our call(s), we can proceed to
// fetch the SERVEROUTPUT explicitly, using
// DBMS_OUTPUT.GET_LINES
try (CallableStatement call = c.prepareCall(
"declare "
+ " num integer := 1000;"
+ "begin "
+ " dbms_output.get_lines(?, num);"
+ "end;"
)) {
call.registerOutParameter(1, Types.ARRAY,
"DBMSOUTPUT_LINESARRAY");
call.execute();
Array array = null;
try {
array = call.getArray(1);
Stream.of((Object[]) array.getArray())
.forEach(System.out::println);
}
finally {
if (array != null)
array.free();
}
}
}
// Don't forget to disable DBMS_OUTPUT for the remaining use
// of the connection.
finally {
s.executeUpdate("begin dbms_output.disable(); end;");
}
}
As can be seen above, this is rather simple:
- Initialise a connection with
DBMS_OUTPUT.ENABLE
- Do the actually interesting work
- Fetch the output and call
DBMS_OUTPUT.DISABLE
This could also be refactored into a utility:
// Alternatively, just use https://github.com/jOOQ/jOOL
interface WhyUNoCheckedExceptionRunnable {
void run() throws Exception;
}
static void logServerOutput(
Connection connection,
WhyUNoCheckedExceptionRunnable runnable
) throws Exception {
try (Statement s = connection.createStatement()) {
try {
s.executeUpdate("begin dbms_output.enable(); end;");
runnable.run();
try (CallableStatement call = connection.prepareCall(
"declare "
+ " num integer := 1000;"
+ "begin "
+ " dbms_output.get_lines(?, num);"
+ "end;"
)) {
call.registerOutParameter(1, Types.ARRAY,
"DBMSOUTPUT_LINESARRAY");
call.execute();
Array array = null;
try {
array = call.getArray(1);
Stream.of((Object[]) array.getArray())
.forEach(System.out::println);
}
finally {
if (array != null)
array.free();
}
}
}
finally {
s.executeUpdate("begin dbms_output.disable(); end;");
}
}
}
This can now be called conveniently as such:
try (Connection c = DriverManager.getConnection(url, properties);
Statement s = c.createStatement()) {
logServerOutput(c, () ->
s.executeUpdate("begin my_procedure(1, 2); end;"));
}
How to do the same with jOOQ?
jOOQ 3.11 will have built in support for fetching this server output through its
ExecuteListener
SPI with
https://github.com/jOOQ/jOOQ/issues/6580
We can either use jOOQ’s plain SQL API as such:
try (Connection c = DriverManager.getConnection(url, properties)) {
// Specify this setting to fetch server output explicitly
DSLContext ctx = DSL.using(c,
new Settings().withFetchServerOutputSize(10));
ctx.execute("begin my_procedure(1, 2); end;");
}
Or, use the code generator for even more type safe calls to the procedures:
try (Connection c = DriverManager.getConnection(url, properties)) {
// Specify this setting to fetch server output explicitly
DSLContext ctx = DSL.using(c,
new Settings().withFetchServerOutputSize(10));
myProcedure(ctx.configuration(), 1, 2);
}
The log output will be:
DEBUG [org.jooq.tools.LoggerListener ] - Executing query : begin my_procedure(1, 2); end;
DEBUG [org.jooq.impl.FetchServerOutputListener] - 2 rows inserted
Like this:
Like Loading...
That’s awesome! Thanks for this great tip and all detailed explanation, Lukas.
I have tried the above and it appears to work. Only problem I have is that in Java – when printing the DBMS_OUTPUT array – “???” is returned for each line…
Thanks for your message. Many things can go wrong along the way, hard to say from your description what went wrong in your case. Perhaps try https://stackoverflow.com ?
Thanks for the quick reply. Actually Marmite Bomber had the same problem and made a comment in Stackoverflow: https://stackoverflow.com/questions/47830370/getting-output-from-dbms-output-get-lines-using-jdbc
Indeed, and as I had already commented there, I don’t think that’s strictly related. Asking a new, specific question about this problem on Stack Overflow, including an MCVE (https://stackoverflow.com/help/mcve) might lead to quick results.
is it possible to get intermediary results from call.execute(); without needing to wait for it’s completion?
Say that the callableStatement produces several lines of output. Is it possible to call the CallableStatement.execute but not wait for the completion of the procedure and obtain intermediary dbms_output while the procedure runs ?
I’m actually not sure, as I’ve never had the use-case. But why not ask on Stack Overflow?