java - Return ROWID Parameter from insert statement using JDBC connection to oracle

Question

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

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?

Answer 1

Usually you don't want to make code database dependent. Instead of OraclePreparedStatement, you should use CallableStatement.

CallableStatement statement = connection.prepareCall("{call INSERT INTO tableA (some_id) VALUES (1) RETURNING ROWID INTO ? }");
statement.registerOutParameter( 1, Types.VARCHAR );

int updateCount = statement.executeUpdate();
if (updateCount > 0) {
   return statement.getString(1);
}