问题描述
我试图了解如何调用一个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
解决方案我不同意。 / p>
如果有一个存储函数或过程返回一个引用游标或者一个引用游标作为
OUT
参数,ref游标作为ResultSet
从JDBC中出来。因此,如果可以使用SYS_REFCURSOR
参数调用存储过程,我怀疑ResultSet
将是你需要通过的。
事实上,我的怀疑得到确认。如果你看看Oracle的扩展
CallableStatement
,,它会继承方法从它的超级接口OraclePreparedStatement
。因此,您可以将CallableStatement
转换为OracleCallableStatement
,调用setCursor $ c $
如果你使用这个方法,尝试在OracleCallableStatement
上调用setCursor
,您会得到一个异常java.sql.SQLException:不支持的功能。
您可以尝试使用
调用
,但你只会得到另一个setObject
> ResultSetjava.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
asIN
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
解决方案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 aResultSet
. So, if it were possible to call a stored procedure with aSYS_REFCURSOR
parameter, I'd suspect that aResultSet
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 asetCursor(int, ResultSet)
method from its superinterfaceOraclePreparedStatement
. Therefore, you could cast theCallableStatement
toOracleCallableStatement
, call thesetCursor
method, and away you go.Except this approach doesn't actually work.
If you try calling
setCursor
on anOracleCallableStatement
, you will get an exceptionjava.sql.SQLException: Unsupported feature
.You can try calling
setObject
with aResultSet
, but you will only get anotherjava.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 singleSYS_REFCURSOR
parameter:get_ref_cursor
returns a ref cursor andprint_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过程的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!