How Can I Retrieve DBMS_OUTPUT Lines from Oracle Procedures Using JDBC?

Extracting DBMS_OUTPUT Lines using JDBC

The dbms_output.get_lines procedure enables retrieval of output from Oracle procedures executed within a Java application using JDBC. To accomplish this without creating database objects, consider the following steps:

1. Enable Server Output Buffering: Use dbms_output.enable() to activate buffering for server output.
2. Populate Buffer: Invoke dbms_output.put_line() to store output lines within the buffer.
3. Retrieve Buffered Lines JDBC: Employ CallableStatement to execute a dynamic query that calls dbms_output.get_lines and registers the retrieved lines as an Array.
4. Disable Output Buffering: Execute dbms_output.disable() to avoid buffer overflow.

Code Example:

try (CallableStatement call = c.prepareCall(
  "declare \n"
  + "  num integer := 1000;\n" // Adjust value as needed
  + "begin \n"
  + "  dbms_output.enable();\n"
  + "  dbms_output.put_line('abc');\n"
  + "  dbms_output.put_line('hello');\n"
  + "  dbms_output.put_line('so cool');\n"
  + "  dbms_output.get_lines(?, num);\n"
  + "  dbms_output.disable();\n"
  + "end;\n"
)) {
  call.registerOutParameter(1, Types.ARRAY, "DBMSOUTPUT_LINESARRAY");
  call.execute();

  Array array = call.getArray(1);
  System.out.println(Arrays.asList((Object[]) array.getArray()));
}

Output:

[abc, hello, so cool, null]

Remember that enabling and disabling the output buffer affects the entire connection. Therefore, it's possible to capture output across multiple JDBC statements.

Note: This approach retrieves a maximum of 1000 lines by default. Consider looping in PL/SQL or polling the database for additional lines.

Using jOOQ:

jOOQ users can automatically fetch server output by enabling the FetchServerOutputListener:

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

This action will log the output in the FetchServerOutputListener and capture the server output in ExecuteContext::serverOutput.

The above is the detailed content of How Can I Retrieve DBMS_OUTPUT Lines from Oracle Procedures Using JDBC?. For more information, please follow other related articles on the PHP Chinese website!