gpt4 book ai didi

java - Oracle JDBC 从过程获取 SYS_REFCURSOR

转载 作者:行者123 更新时间:2023-11-30 01:58:51 31 4
gpt4 key购买 nike

我正在使用 Oracle 12g,并尝试从 SYS_REFCURSOR 获取 ResultSet

我有一个带有以下签名的过程:

CREATE OR REPLACE PROCEDURE proc_search(user IN VARCHAR2, res OUT SYS_REFCURSOR)

这是我正在使用的 Java JDBC 代码:

try {
CallableStatement cstmt = con.prepareCall("exec proc_search(?, ?)");
cstmt.setString(1, login);
cstmt.registerOutParameter(2, Types.REF);
cstmt.execute();
ResultSet rs = (ResultSet)cstmt.getObject(2);

while (rs.next()) {
System.out.println(rs.getString(1));
}
} catch (Exception e) {
System.err.println(e);
e.printStackTrace();
}

但是,我收到以下错误:

java.sql.SQLException: ORA-03115: unsupported network datatype or representation

最佳答案

首先,getCursor方法应该用于SYS_REFCURSOR而不是getObject

其次,prepareCall应该有BEGINEND

代码片段

try {
CallableStatement cstmt = con.prepareCall("BEGIN proc_search(?, ?); END;");
cstmt.setString(1, login);
cstmt.registerOutParameter(2, OracleTypes.CURSOR);
cstmt.execute();
ResultSet rs = ((OracleCallableStatement)cstmt).getCursor(2);

while (rs.next()) {
System.out.println(rs.getString("name"));
}
} catch (Exception e) {
System.err.println(e);
e.printStackTrace();
}

顺便说一句,必须有一个finally block 来关闭Connection、CallableStatement、ResultSet 等。

finally {
try {
if (con != null)
con.close();

if (cstmt!= null)
cstmt.close();

if (rs!= null)
rs.close();

} catch (SQLException sqlexc) {
sqlexc.printStackTrace();
}
}

更新1

使用CallableStatement

    Connection conn = null;
CallableStatement callableStatement = null;
ResultSet rs = null;

try {
conn = getconn();
callableStatement = conn.prepareCall("{call proc_search(?, ?)}");
callableStatement.setString(1, login);
callableStatement.registerOutParameter(2, OracleTypes.CURSOR);

callableStatement.executeUpdate();

rs = (ResultSet) callableStatement.getObject(2);
while (rs.next()) {
String userid = rs.getString("name");
}

} catch (SQLException e) {

System.out.println(e.getMessage());
e.printStackTrace();

} finally {

if (rs != null) {
rs.close();
}

if (callableStatement != null) {
callableStatement.close();
}

if (conn != null) {
conn.close();
}

}

}

关于java - Oracle JDBC 从过程获取 SYS_REFCURSOR,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/53531178/

31 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com