使用JDBC连接从oracle返回插入语句的ROWID参数 [英] Return ROWID Parameter from insert statement using JDBC connection to oracle

问题描述

我似乎无法使用正确的魔法组合来完成这项工作：

I can't seem to get the right magic combination to make this work:

OracleDataSource ods = new oracle.jdbc.pool.OracleDataSource();
ods.setURL("jdbc:oracle:thin:app_user/pass@server:1521:sid");
DefaultContext conn = ods.getConnection();
CallableStatement st = conn.prepareCall("INSERT INTO tableA (some_id) VALUES (1) RETURNING ROWID INTO :rowid0");
st.registerReturnParameter(1, OracleTypes.ROWID);
st.execute();

我得到的错误是违反协议。如果我改为registerOutParameter（），我会收到通知，说明我没有注册所有的返回变量。如果我将语句包装在PL / SQL中开始;结束;然后我使用常规的registerOutParameter（）调用得到参数。我真的更愿意避免在PL / SQL中包装所有的insert语句 - 所以上面缺少什么？

The error I get is "Protocol Violation". If I change to registerOutParameter(), I get notified that I haven't registered all return variables. If I wrap the statement in a PL/SQL begin; end; block then I get the parameter just fine using a regular registerOutParameter() call. I would really prefer to avoid wrapping all my inserts statements in PL/SQL - so what is missing from above?

推荐答案

一些你需要做的事情

• 将CallableStatement更改为OracleCallableStatement


• 尝试并返回NUMBER ，即：OracleTypes.Number

从查询中返回信息的示例代码：

Sample code for returning info from a query:

OraclePreparedStatement pstmt = (OraclePreparedStatement)conn.prepareStatement(
       "delete from tab1 where age < ? returning name into ?");
pstmt.setInt(1,18);

/** register returned parameter
  * in this case the maximum size of name is 100 chars
  */
pstmt.registerReturnParameter(2, OracleTypes.VARCHAR, 100);

// process the DML returning statement
count = pstmt.executeUpdate();
if (count>0)
{
  ResultSet rset = pstmt.getReturnResultSet(); //rest is not null and not empty
  while(rset.next())
  {
    String name = rset.getString(1);
    ...
  }
}

关于Oracle的更多信息JDBC扩展：

More info on Oracle's JDBC extensions:

• http://download-uk.oracle.com/docs/cd/B19306_01/java.102/b14355/oraint.htm

• http://download-uk.oracle.com/docs/cd/B19306_01/java.102/b14355/oraint.htm

这篇关于使用JDBC连接从oracle返回插入语句的ROWID参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！