How to Retrieve Oracle's DBMS_OUTPUT Using JDBC?

Retrieving DBMS_OUTPUT.get_lines Using JDBC

When utilizing Oracle's dbms_output.get_lines within a Java application, JDBC provides a means of obtaining the output without creating additional database objects. This approach involves a series of steps:

1. Enable Server Output Buffering: Use dbms_output.enable() to enable server output buffering.
2. Generate Output: Execute any stored procedures or scripts that produce output and should be captured.
3. Fetch Output using DBMS_OUTPUT.get_lines: Use the CallableStatement interface to execute a procedure that invokes dbms_output.get_lines and register an Array object as the output parameter.
4. Disable Server Output Buffering: Invoke dbms_output.disable() to prevent buffer overflow.

Here's a sample code snippet that illustrates this process:

try (CallableStatement call = c.prepareCall(
    "declare "
  + "  num integer := 1000;" // Adapt this as needed
  + "begin "
  + "  dbms_output.enable();"
  + "  dbms_output.put_line('abc');"
  + "  dbms_output.put_line('hello');"
  + "  dbms_output.put_line('so cool');"

  // This captures the output up until now through a PL/SQL TABLE type.
  // Oracle 12c+ uses a SQL cursor, while 11g requires an auxiliary SQL TABLE
  + "  dbms_output.get_lines(?, num);"

  // Disable buffering
  + "  dbms_output.disable();"
  + "end;"
)) {
    call.registerOutParameter(1, Types.ARRAY, "DBMSOUTPUT_LINESARRAY");
    call.execute();

    Array array = null;
    try {
        array = call.getArray(1);
        System.out.println(Arrays.asList((Object[]) array.getArray()));
    }
    finally {
        if (array != null)
            array.free();
    }
}

Using jOOQ

If using the jOOQ library, you can automatically retrieve server output for queries by enabling it in the Settings object:

DSLContext ctx = DSL.using(c, 
    new Settings().withFetchServerOutputSize(10));

The server output will be available in the ExecuteContext::serverOutput after query execution.

Caution against DBMS_OUTPUT.GET_LINE

While DBMS_OUTPUT.GET_LINE retrieves lines individually, benchmarks have shown a significant slowdown compared to using DBMS_OUTPUT.GET_LINES, even in PL/SQL. Therefore, the bulk approach utilizing DBMS_OUTPUT.GET_LINES is recommended for efficiency.

The above is the detailed content of How to Retrieve Oracle's DBMS_OUTPUT Using JDBC?. For more information, please follow other related articles on the PHP Chinese website!