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

Answer 2

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 3

PreparedStatement prepareStatement = connection.prepareStatement("insert...",
            new String[] { "your_primary_key_column_name" });

    prepareStatement.executeUpdate();

    ResultSet generatedKeys = prepareStatement.getGeneratedKeys();
    if (null != generatedKeys && generatedKeys.next()) {
         Long primaryKey = generatedKeys.getLong(1);
    }

I have found the answer this is perfectly works. I can insert from JAVA and its return with the key.

Full version:

CREATE TABLE STUDENTS
(
   STUDENT_ID   NUMBER NOT NULL PRIMARY KEY,
   NAME         VARCHAR2 (50 BYTE),
   EMAIL        VARCHAR2 (50 BYTE),
   BIRTH_DATE   DATE
);


CREATE SEQUENCE STUDENT_SEQ
   START WITH 0
   MAXVALUE 9999999999999999999999999999
   MINVALUE 0;

And the Java code

String QUERY = "INSERT INTO students "+
               "  VALUES (student_seq.NEXTVAL,"+
               "         'Harry', 'harry@hogwarts.edu', '31-July-1980')";

// load oracle driver
Class.forName("oracle.jdbc.driver.OracleDriver");

// get database connection from connection string
Connection connection = DriverManager.getConnection(
        "jdbc:oracle:thin:@localhost:1521:sample", "scott", "tiger");

// prepare statement to execute insert query
// note the 2nd argument passed to prepareStatement() method
// pass name of primary key column, in this case student_id is
// generated from sequence
PreparedStatement ps = connection.prepareStatement(QUERY,
        new String[] { "student_id" });

// local variable to hold auto generated student id
Long studentId = null;

// execute the insert statement, if success get the primary key value
if (ps.executeUpdate() > 0) {

    // getGeneratedKeys() returns result set of keys that were auto
    // generated
    // in our case student_id column
    ResultSet generatedKeys = ps.getGeneratedKeys();

    // if resultset has data, get the primary key value
    // of last inserted record
    if (null != generatedKeys && generatedKeys.next()) {

        // voila! we got student id which was generated from sequence
        studentId = generatedKeys.getLong(1);
    }

}

source : http://viralpatel.net/blogs/oracle-java-jdbc-get-primary-key-insert-sql/

Answer 4

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 5

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