How to Retrieve DBMS_OUTPUT from Oracle Using JDBC?

Retrieving Output from Oracle's DBMS_OUTPUT.GET_LINES Using JDBC

JDBC provides a means to access the output generated by Oracle's dbms_output.get_lines procedure without creating additional objects in the database. Here's how to achieve this:

1. Enable Server Output Buffering: Before accessing the output, enable buffering on the current connection to capture any server output:

   dbms_output.enable();

2. Store Output in PL/SQL Table: To capture the server output, create a table in PL/SQL to store the output:

   create type DBMSOUTPUT_LINESARRAY as table of varchar2(255);

3. Call DBMS_OUTPUT.GET_LINES: Use a CallableStatement to call dbms_output.get_lines and pass in the PL/SQL table as an out parameter:

   CallableStatement call = c.prepareCall(
    "declare \n"
     + "  num integer := 1000;\n" // Adjust this value as needed
     + "begin \n"
   
     + "  dbms_output.get_lines(?, num);\n"
   
     + "  dbms_output.disable();\n"
     + "end;\n"
   );
   call.registerOutParameter(1, Types.ARRAY, "DBMSOUTPUT_LINESARRAY");

4. Disable Server Output Buffering: After retrieving the output, disable buffering to prevent potential buffer overflows:

   dbms_output.disable();

5. Access the Output: The output is available in the out parameter. You can use the JDBC API to access the elements of the table.

6. Example Code: Here's a code snippet to demonstrate the process:

   try (CallableStatement call = c.prepareCall(
    "declare \n"
     + "  num integer := 1000;\n" 
     + "begin \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 = null;
    try {
        array = call.getArray(1);
        System.out.println(Arrays.asList((Object[]) array.getArray()));
    }
    finally {
        if (array != null)
            array.free();
    }
   }

Note that this approach works with Oracle 12c or later. In Oracle 11g, you may need to use an auxiliary SQL TABLE type.

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