How to Fetch Oracle's DBMS_OUTPUT.GET_LINES in JDBC Without Creating Database Objects?

Fetching DBMS_OUTPUT.GET_LINES in JDBC Without Creating Database Objects

In Oracle, DBMS_OUTPUT.GET_LINES provides a mechanism to retrieve output from stored procedures or other database operations. While it's possible to create additional objects in the database to facilitate this process, it can be desirable to avoid this overhead. This article explores how to obtain DBMS_OUTPUT.GET_LINES output in Java using JDBC without resorting to external database entities.

Enabling Output Buffering

The first step is to enable output buffering using DBMS_OUTPUT.ENABLE(). This allows for capturing server output while database operations are executed.

Capturing Output

To capture the output, DBMS_OUTPUT.GET_LINES() is used. This procedure retrieves a specified number of lines of output into a SQL cursor. It's crucial to register the output parameter as an array of type "DBMSOUTPUT_LINESARRAY" to receive the results.

Retrieving Output

The DBMSOUTPUT_LINESARRAY contains an array of objects representing the output lines. To obtain the actual output, the array is retrieved using JDBC's getArray() method.

Example Code

try (CallableStatement call = c.prepareCall(
    //这段代码用于设置enable output buffering,在DBMS_OUTPUT.PUT_LINE()与DBMS_OUTPUT.GET_LINES()之间执行
    "declare num integer := 1000; begin dbms_output.enable(); ..."
)) {
    // ... remaining code to capture and print output
}

jOOQ Integration

jOOQ, a Java library for working with SQL, provides a convenient way to automatically fetch output from server executions. By setting withFetchServerOutputSize() in the Settings object, jOOQ will retrieve server output upon query execution.

Recommendation: DBMS_OUTPUT.GET_LINES over DBMS_OUTPUT.GET_LINE

While DBMS_OUTPUT.GET_LINE returns single lines at a time, it's highly recommended to use DBMS_OUTPUT.GET_LINES instead. Benchmarks have shown that DBMS_OUTPUT.GET_LINES is significantly faster when called from JDBC.

The above is the detailed content of How to Fetch Oracle's DBMS_OUTPUT.GET_LINES in JDBC Without Creating Database Objects?. For more information, please follow other related articles on the PHP Chinese website!