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

问题描述

我试图了解如何调用一个PL / SQL过程，它将 SYS_REFCURSOR 作为 IN 参数。

请考虑以下PL / SQL过程：

 code> print_cursor_contents（myCursor SYS_REFCURSOR，row_count OUT NUMBER）; 
 

将值绑定到IN参数 setXXX 方法我使用？

对我来说，一个带有单独游标记录字段的java类，因为它的成员和这个类的实例数组似乎是表示plsql CURSOR的正确方法。当我这样做时，我得到一个SQLException：

我使用以下set方法

  callStmt.setObject（1，curRec）; 
  

这是我使用上面的语句时遇到的异常：

 在数据库中发生异常
异常消息：列类型无效
 java.sql.SQLException：无效的列类型
在oracle。 jdbc.driver.OraclePreparedStatement.setObjectCritical（OraclePreparedStatement.java:8921）
在oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal（OraclePreparedStatement.java:8396）
在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）
数据库错误代码：17004 
  
 
 
解决方案
 
 
 
字段，因为它的成员和该类的实例数组似乎是表示plsql CURSOR的正确方式。
 
 
我不同意。 / p> 
 
 
如果有一个存储函数或过程返回一个引用游标或者一个引用游标作为 OUT 参数，ref游标作为 ResultSet 从JDBC中出来。因此，如果可以使用 SYS_REFCURSOR 参数调用存储过程，我怀疑 ResultSet 将是你需要通过的。
 
 
 
事实上，我的怀疑得到确认。如果你看看Oracle的扩展 CallableStatement ，  OracleCallableStatement  ，它会继承  setCursor（int，ResultSet） 方法从它的超级接口 OraclePreparedStatement 。因此，您可以将 CallableStatement 转换为 OracleCallableStatement ，调用 setCursor  
 
 
 
如果你使用这个方法，尝试在 OracleCallableStatement 上调用 setCursor ，您会得到一个异常 java.sql.SQLException：不支持的功能。
 
 
 
您可以尝试使用调用 setObject  > ResultSet ，但你只会得到另一个 java.sql.SQLException：无效的列类型异常。
 
 
 
这里是一个测试类，你可以运行验证这两种情况。它调用一个存储过程来获取一个ref游标（因此一个 ResultSet ），然后尝试将它传递给另一个：
  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 }））{
 
 //取消注释下一行调用setCursor：
 //（（OracleCallableStatement）cstmt2）.setCursor（1，rSet）; 
 
 //取消注释下一行来调用setObject：
 // cstmt2.setObject（1，rSet）; 
 
 cstmt2.execute（）; 
} 
} 
} 
} 
} 
  
 
 $ b b 
（ java_ref_curs_test 中的两个过程需要一个 SYS_REFCURSOR 参数： get_ref_cursor 返回一个引用游标，并且 print_refcursor 以一个作为参数，但不执行任何操作。）
 
 
 
所以，你应该使用 setXXX 方法吗？我不会说他们。你所要求的是不可能直接。
 
 
 
它仍然可以调用这个过程，但你必须在PL / SQL中创建ref游标，而不是然后将它传递给你的过程。
 
 
 
例如，我可以使用下面的PL / SQL块来调用上面例子中使用的两个过程： / p> 
 
 
  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（）。
 
I am trying to understand how I can call a PL/SQL procedure which takes a SYS_REFCURSOR as IN parameter.

Consider the following PL/SQL procedure:
print_cursor_contents(myCursor SYS_REFCURSOR , row_count OUT NUMBER);
At the time of binding value to the IN parameter which setXXX method do I use ? 

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:

I used the following set method
         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

解决方案

  
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 disagree.

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.

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.

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

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

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();
                }
            }
        }
    }
}
(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.)

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

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.

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;
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屋！