使用 JDBC 以 SYS_REFCURSOR 作为 IN 参数调用 PL/SQL 过程 [英] Calling PL/SQL procedure with SYS_REFCURSOR as IN parameter using JDBC

问题描述

我想了解如何调用一个将 SYS_REFCURSOR 作为 IN 参数的 PL/SQL 过程.

I am trying to understand how I can call a PL/SQL procedure which takes a SYS_REFCURSOR as IN parameter.

考虑以下 PL/SQL 过程:

Consider the following PL/SQL procedure:

print_cursor_contents(myCursor SYS_REFCURSOR , row_count OUT NUMBER);

在将值绑定到 IN 参数时，我使用哪种 setXXX 方法?

At the time of binding value to the IN parameter which setXXX method do I use ?

对我来说，一个带有单个游标记录字段的 java 类，因为它的成员和此类的实例数组似乎是表示 plsql CURSOR 的正确方法.执行此操作时出现 SQLException:

To me a java Class with individual cursor record fields , as it members and a Array of instances of this class seems the proper way to represent a plsql CURSOR. I get a SQLException when I do this:

我使用了以下设置方法

         callStmt.setObject(1, curRec);

这是我使用上述语句得到的异常:

Here is the exception I got for using the above statement:

Exception occured in the database
Exception message: Invalid column type
java.sql.SQLException: Invalid column type
    at oracle.jdbc.driver.OraclePreparedStatement.setObjectCritical(OraclePreparedStatement.java:8921)
    at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:8396)
    at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:9176)
    at oracle.jdbc.driver.OracleCallableStatement.setObject(OracleCallableStatement.java:5024)
    at oracle.jdbc.driver.OraclePreparedStatementWrapper.setObject(OraclePreparedStatementWrapper.java:234)
    at com.rolta.HrManager.printMaxSalAllDept(HrManager.java:1022)
    at com.rolta.HrManager.main(HrManager.java:1116)
Database error code: 17004

推荐答案

对我来说，一个带有单个游标记录字段的 java 类，因为它的成员和此类的实例数组似乎是表示 plsql CURSOR 的正确方法.

To me a java Class with individual cursor record fields , as it members and a Array of instances of this class seems the proper way to represent a plsql CURSOR.

我不同意.

如果您有一个存储函数或过程，它返回一个引用游标或将一个引用游标作为 OUT 参数，则引用游标作为 ResultSet 从 JDBC 出来>.因此，如果可以使用 SYS_REFCURSOR 参数调用存储过程，我怀疑 ResultSet 将是您需要传递的内容.

If you have a stored function or procedure that either returns a ref cursor or has a ref cursor as an OUT parameter, the ref cursor comes out of JDBC as a ResultSet. So, if it were possible to call a stored procedure with a SYS_REFCURSOR parameter, I'd suspect that a ResultSet would be what you would need to pass.

事实上，我的怀疑得到了证实.如果您查看 Oracle 对 CallableStatement 的扩展，OracleCallableStatement，它继承了一个setCursor(int, ResultSet) 方法来自其超接口 OraclePreparedStatement.因此，您可以将 CallableStatement 转换为 OracleCallableStatement，调用 setCursor 方法，然后就可以了.

In fact, my suspicions are confirmed. If you take a look at Oracle's extension to CallableStatement, OracleCallableStatement, it inherits a setCursor(int, ResultSet) method from its superinterface OraclePreparedStatement. Therefore, you could cast the CallableStatement to OracleCallableStatement, call the setCursor method, and away you go.

除非这种方法实际上不起作用.

Except this approach doesn't actually work.

如果您尝试在 OracleCallableStatement 上调用 setCursor，您将收到异常 java.sql.SQLException: Unsupported feature.

If you try calling setCursor on an OracleCallableStatement, you will get an exception java.sql.SQLException: Unsupported feature.

您可以尝试使用 ResultSet 调用 setObject，但您只会得到另一个 java.sql.SQLException: Invalid column type 异常.

You can try callingsetObject with a ResultSet, but you will only get another java.sql.SQLException: Invalid column type exception.

这是一个测试类，您可以运行以验证这两种情况.它调用一个存储过程来获取一个引用游标(因此是一个 ResultSet)，然后尝试将它传递给另一个:

Here's a test class you can run to verify either case. It calls one stored procedure to get a ref cursor (and hence a ResultSet) and then tries to pass it to the other:

import java.sql.*;
import oracle.jdbc.OracleTypes;
import oracle.jdbc.OracleCallableStatement;

public class JavaRefCursorTest {
    public static void main(String[] args) throws Exception {
        Connection conn = DriverManager.getConnection(
                "jdbc:oracle:thin:@localhost:1521:XE", "user", "password");

        try (CallableStatement cstmt1 = conn.prepareCall(
                "{ call java_ref_curs_test.get_ref_cursor(?)}")) {
            cstmt1.registerOutParameter(1, OracleTypes.CURSOR);
            cstmt1.execute();

            try (ResultSet rSet = (ResultSet)cstmt1.getObject(1)) {
                try (CallableStatement cstmt2 = conn.prepareCall(
                        "{ call java_ref_curs_test.print_refcursor(?)}")) {

                    // Uncomment the next line to call setCursor:
                    // ((OracleCallableStatement)cstmt2).setCursor(1, rSet); 

                    // Uncomment the next line to call setObject:
                    // cstmt2.setObject(1, rSet);

                    cstmt2.execute();
                }
            }
        }
    }
}

(java_ref_curs_test 中的两个过程采用单个 SYS_REFCURSOR 参数:get_ref_cursor 返回一个引用游标和 print_refcursor> 将一个作为参数，但什么都不做.)

(The two procedures in the java_ref_curs_test take a single SYS_REFCURSOR parameter: get_ref_cursor returns a ref cursor and print_refcursor takes one as a parameter but does nothing with it.)

那么，您应该使用哪种 setXXX 方法?我不会说他们中的任何一个.您所要求的无法直接实现.

So, which setXXX method should you use? I would say none of them. What you are asking for is not possible directly.

仍然可以调用此过程，但您必须在 PL/SQL 中创建引用游标，而不是在 Java 中，然后将其传递给您的过程.

It may still be possible to call this procedure, but you will have to create the ref cursor in PL/SQL, not in Java, and then pass it to your procedure.

例如，我可以使用下面的 PL/SQL 块来调用上面例子中使用的两个过程:

For example, I could use the following PL/SQL block to call the two procedures used in the above example:

DECLARE
   l_curs   SYS_REFCURSOR;
BEGIN
   java_ref_curs_test.get_ref_cursor(l_curs);
   java_ref_curs_test.print_refcursor(l_curs); 
END;

你可以很容易地从 JDBC 运行它:把它放在一个字符串中并将它传递给 Statement.executeUpdate().

You can fairly easily run this from JDBC: put it in a string and pass it to Statement.executeUpdate().

这篇关于使用 JDBC 以 SYS_REFCURSOR 作为 IN 参数调用 PL/SQL 过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！