Get last insert id with Oracle 11g using JDBC

Question

I'm new to using Oracle so I'm going off what has already been previously answered in this SO question. I just can't seem to get it to work. Here's the statement that I'm using:

declare
  lastId number;
begin
INSERT INTO "DB_OWNER"."FOO" 
  (ID, DEPARTMENT, BUSINESS)
  VALUES (FOO_ID_SEQ.NEXTVAL, 'Database Management', 'Oracle')
  RETURNING ID INTO lastId;
end;

When I call executeQuery the PreparedStatement that I have made, it inserts everything into the database just fine. However, I cannot seem to figure out how to retrieve the ID. The returned ResultSet object will not work for me. Calling

if(resultSet.next()) ...

yields a nasty SQLException that reads:

Cannot perform fetch on a PLSQL statement: next

How do I get that lastId? Obviously I'm doing it wrong.

Answer 1

make it a function that returns it to you (instead of a procedure). Or, have a procedure with an OUT parameter.

Answer 2

Not sure if this will work, since I've purged all of my computers of anything Oracle, but...

Change your declare to:

declare
  lastId OUT number;

Switch your statement from a PreparedStatement to a CallableStatement by using prepareCall() on your connection. Then register the output parameter before your call, and read it after the update:

cstmt.registerOutParameter(1, java.sql.Types.NUMERIC);
cstmt.executeUpdate();
int x = cstmt.getInt(1);

Answer 3

I tried with Oracle driver v11.2.0.3.0 (since there are some bugs in 10.x and 11.1.x, see other blog). Following code works fine:

final String sql = "insert into TABLE(SOME_COL, OTHER_COL) values (?, ?)";
PreparedStatement ps = con.prepareStatement(sql, new String[] {"ID"});
ps.setLong(1, 264);
ps.setLong(2, 1);
int executeUpdate = ps.executeUpdate();
ResultSet rs = ps.getGeneratedKeys();
if (rs.next() ) {
    // The generated id
    long id = rs.getLong(1);
    System.out.println("executeUpdate: " + executeUpdate + ", id: " + id);
}

Answer 4

When you prepare the statement set the second parameter to RETURN_GENERATED_KEYS. Then you should be able to get a ResultSet off the statement object.