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

Try using ? instead of :rowid0 on your SQL string. I have had problems before with named parameters and Oracle.

Answer 2

Don't know if this applies or not since you don't specify what version you're using.

From Oracle Metalink:

Cause

In the 10.1.0.x JDBC driver, returning DML is not supported:

Per the JDBC FAQ: "10.1.0 (10g r1) Is DML Returning Supported ? Not in the current drivers. However, we do have plans to support it in post 10.1.0 drivers. We really mean it this time."

As the application code is trying to use unsupported JDBC features, errors are raised.

Solution

Upgrade the JDBC driver to 10.2.0.x, because per the FAQ the 10.2.0.x JDBC drivers do support returning clause:

"10.2.0 (10g r2) Is DML Returning Supported ? YES! And it's about time. See the Developer's Guide for details. "

EDIT Just for grins, you can check the version of JDBC Oracle thinks it's using with:

 // Create Oracle DatabaseMetaData object
  DatabaseMetaData meta = conn.getMetaData();

  // gets driver info:
  System.out.println("JDBC driver version is " + meta.getDriverVersion());

If that shows a JDBC driver 10.2.0.x or later, then I'm out of ideas and perhaps a support request to oracle is in order...

Answer 3

A few things you'll need to do

• Change CallableStatement to OracleCallableStatement
• Try and return into a NUMBER, ie: 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);
    ...
  }
}

More info on Oracle's JDBC extensions:

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

Answer 4

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 i = statement.executeUpdate();    
if (i > 0) // Update count
   return statement.getString(1);