如何从Java中的Oracle SP返回sys_refcursor? [英] How do I return a sys_refcursor from oracle SP in java?

问题描述

我在oracle中有一个存储过程(SP):

I have a stored procedure (SP) in oracle:

CREATE OR REPLACE 
PROCEDURE "SP_SEL_LOGIN_INFO" (
p_username IN varchar2,
p_ResultSet OUT sys_refcursor
) AS

begin

OPEN p_ResultSet FOR
SELECT * FROM user_accounts
WHERE p_username = username;

end;

在我的java类中，我有以下几行代码可以调用SP:

In my java class I have the following lines of code to call the SP:

currentCon = connectionpackage.ConnectionManager.getConnection(dbSource);
CallableStatement stmt = currentCon.prepareCall("{call SP_SEL_LOGIN_INFO(?, ?)}");
stmt.setString(1, username); 
stmt.registerOutParameter(2, OracleTypes.CURSOR); //REF CURSOR
stmt.execute();
rs = stmt.getCursor(2);

这就是我在网上找到的调用SP并返回游标的内容.当我尝试编译时，出现以下两个错误:

That is what I have found online to call a SP and return a cursor. When I try to compile I get the following two errors:

error: cannot find symbol stmt.registerOutParameter(2, OracleTypes.CURSOR);和error: cannot find symbol rs = stmt.getCursor(2);表示找不到OracleTypes和getCursor的地方.

error: cannot find symbol stmt.registerOutParameter(2, OracleTypes.CURSOR); AND error: cannot find symbol rs = stmt.getCursor(2); Where it says it cannot find OracleTypes and getCursor.

我尝试导入import oracle.jdbc.driver.*;或import oracle.jdbc.*;并分别得到错误error: package oracle.jdbc does not exist import oracle.jdbc.driver.*;和error: package oracle.jdbc does not exist import oracle.jdbc.*;.

I tried importing import oracle.jdbc.driver.*; or import oracle.jdbc.*; and got the errors error: package oracle.jdbc does not exist import oracle.jdbc.driver.*; and error: package oracle.jdbc does not exist import oracle.jdbc.*; respectively.

我在适当的文件夹中也有ojdbc14.jar文件，可以使用查询字符串进行连接.只是在尝试使用SP时，它给我带来了麻烦.

I also have the ojdbc14.jar file in the proper folder and can connect using a query string. It is just when trying to use the SP that it is giving me trouble.

SP是我们在当前CF网站上使用的SP，因此我想按原样重用它.有人可以说明为什么这可能行不通吗?或者，如果还有其他代码可用于从Oracle SP返回游标?谢谢.

The SP is one we use on our current CF website, so I would like to just reuse it as is. Could someone please shed some light on why this may not be working? Or if there is an alternative bit of code to use to return a cursor from an Oracle SP? Thanks.

推荐答案

请尝试此操作，它可能会解决问题.

Please try this, it might solve the issue.

替换此

rs = stmt.getCursor(2);

与

 rs = ((OracleCallableStatement)stmt).getCursor(2);

除了用于调用过程之外，还使用以下语句.

Besides for calling your procedure use this statement.

CallableStatement stmt = conn.prepareCall("BEGIN SP_SEL_LOGIN_INFO(?, ?); END;");

这篇关于如何从Java中的Oracle SP返回sys_refcursor?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！