使用JDBC从dbms_output.get_lines获取输出 [英] Getting output from dbms_output.get_lines using JDBC

问题描述

如何使用JDBC 在Java应用程序中获取Oracle的 dbms_output.get_lines 的输出，而无需在数据库中创建其他对象？

How can one get the output from Oracle's dbms_output.get_lines in a Java app using JDBC without creating additional objects in the database?

推荐答案

我也在这里发表了关于这个问题的博客。这是一个片段，说明了如何做到这一点：

I've also blogged about this issue here. Here's a snippet that illustrates how this can be done:

try (CallableStatement call = c.prepareCall(
    "declare "
  + "  num integer := 1000;" // Adapt this as needed
  + "begin "

  // You have to enable buffering any server output that you may want to fetch
  + "  dbms_output.enable();"

  // This might as well be a call to third-party stored procedures, etc., whose
  // output you want to capture
  + "  dbms_output.put_line('abc');"
  + "  dbms_output.put_line('hello');"
  + "  dbms_output.put_line('so cool');"

  // This is again your call here to capture the output up until now.
  // The below fetching the PL/SQL TABLE type into a SQL cursor works with Oracle 12c.
  // In an 11g version, you'd need an auxiliary SQL TABLE type
  + "  dbms_output.get_lines(?, num);"

  // Don't forget this or the buffer will overflow eventually
  + "  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();
    }
}

以上将打印：

[abc, hello, so cool, null]

请注意 ENABLE / DISABLE 设置是一个连接宽设置，所以你也可以在几个JDBC语句中执行此操作：

Note that the ENABLE / DISABLE setting is a connection wide setting, so you can also do this over several JDBC statements:

try (Connection c = DriverManager.getConnection(url, properties);
     Statement s = c.createStatement()) {

    try {
        s.executeUpdate("begin dbms_output.enable(); end;");
        s.executeUpdate("begin dbms_output.put_line('abc'); end;");
        s.executeUpdate("begin dbms_output.put_line('hello'); end;");
        s.executeUpdate("begin dbms_output.put_line('so cool'); end;");

        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);
                System.out.println(Arrays.asList((Object[]) array.getArray()));
            }
            finally {
                if (array != null)
                    array.free();
            }
        }
    }
    finally {
        s.executeUpdate("begin dbms_output.disable(); end;");
    }
}

另请注意，这将获取固定大小1000最多行。如果你想要更多行，你可能需要循环PL / SQL或轮询数据库。

Note also that this will fetch a fixed size of 1000 lines at most. You may need to loop in PL/SQL or poll the database if you want more lines.

以前，有一个现在删除的答案建议单独调用 DBMS_OUTPUT.GET_LINE 相反，它一次返回一行。我已经将该方法与 DBMS_OUTPUT.GET_LINES 进行比较，并且差异非常大 - 从JDBC调用时速度降低了30倍（即使实际上并不是很大）从PL / SQL调用过程时的差异。

Previously, there was a now-deleted answer that suggested individual calls to DBMS_OUTPUT.GET_LINE instead, which returns one line at a time. I've benchmarked the approach comparing it with DBMS_OUTPUT.GET_LINES, and the differences are drastic - up to a factor 30x slower when called from JDBC (even if there's not really a big difference when calling the procedures from PL/SQL).

因此，使用 DBMS_OUTPUT.GET_LINES 的批量数据传输方法绝对值得。以下是基准测试的链接：

So, the bulk data transferring approach using DBMS_OUTPUT.GET_LINES is definitely worth it. Here's a link to the benchmark:

https://blog.jooq.org/2017/12/18/the-cost-of-jdbc-server-roundtrips/

这篇关于使用JDBC从dbms_output.get_lines获取输出的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！