Get last insert id with Oracle 11g using JDBC
Solution 1
make it a function that returns it to you (instead of a procedure). Or, have a procedure with an OUT parameter.
Solution 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);
Solution 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);
}
Solution 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.
Solution 5
Are you doing that in a stored procedure ? According to this Oracle document, it won't work with the server-side driver.
The Oracle server-side internal driver does not support the retrieval of auto-generated keys feature.
Comments
-
Jim almost 2 years
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. Callingif(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.