5

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.

Community
  • 1
  • 1
geowa4
  • 40,390
  • 17
  • 88
  • 107
  • You could always query `SELECT FOO_ID_SEQ.CURRVAL FROM DUAL`. – Phil Feb 10 '11 at 00:52
  • 2
    Post the function or stored procedure -- need to know if you set `lastid` as an INOUT parameter. – OMG Ponies Feb 10 '11 at 00:54
  • if i have to execute another query, i'm not guaranteed that it's the id of the element i just inserted. there could be another query that snuck in there. – geowa4 Feb 10 '11 at 00:54
  • 1
    `CURRVAL` is session safe, but the `RETURNING` clause will allow you to do two things in one statement. – OMG Ponies Feb 10 '11 at 00:54
  • @OMGPonies One of the biggest "lightbulb" moments a budding Oracle developer gets to experience ;) – Phil Feb 10 '11 at 00:56
  • @OMGPonies, that is all i have. I wrap that query in a PreparedStatement and call executeQuery on it. – geowa4 Feb 10 '11 at 01:19
  • You're running this as an anonymous PLSQL block? The java resultset object IME needs Oracle to return a SYS_REFCURSOR to iterate through the results - this isn't what an anonymous PLSQL block is intended for, it should be in a stored procedure (preferably within a package). – OMG Ponies Feb 10 '11 at 01:26
  • ah, well that makes sense. you said that currval is session safe. so that should work for my needs after an insert? i just need the id of the record i just inserted. if that will be safe, i'll just go that route. in the future, i'm planning on making stored procedure, but i need a simple demo for now. – geowa4 Feb 10 '11 at 01:36
  • Use `getGeneratedKeys()`... this is what it's for. I'd also suggest using a trigger to generate the ID rather than using Oracle-specific SQL in your application. – ColinD Feb 10 '11 at 05:31

6 Answers6

2

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);
}
Community
  • 1
  • 1
Franz
  • 119
  • 3
  • 8
2

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

tbone
  • 36
  • 1
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);
Jason Gritman
  • 5,251
  • 4
  • 30
  • 38
1

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.

jzd
  • 23,473
  • 9
  • 54
  • 76
  • @James, possibly but that seems like a strange question, because you really shouldn't have multiple threads working with the same keys and statements. Could you expand on your question? – jzd Feb 10 '11 at 12:13
  • Sure. If you have a single DAO instance (no synchonized) and more than one person working on a database (some sort of server app) this would produce unreliable behaviour. I'm supposing the database returns this value on a first-come, first-served basis so would it suit a single user app? I suppose Hibernate's `EntityManager` handles any issues with state (persisted or not) using a Proxy pattern or something along those lines. – James P. Feb 11 '11 at 00:29
1

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.
ColinD
  • 108,630
  • 30
  • 201
  • 202
RealHowTo
  • 34,977
  • 11
  • 70
  • 85
1

You can use Statement.getGeneratedKeys() to do this. You just need to make sure to tell JDBC what columns you want back using one of the method overloads for that, such as the Connection.prepareStatement overload here:

Connection conn = ...
PreparedStatement pS = conn.prepareStatement(sql, new String[]{"id"});
pS.executeUpdate();
ResultSet rS = pS.getGeneratedKeys();
if (rS.next()) {
  long id = rS.getLong("id");
  ...
}

You don't need to do the RETURNING x INTO stuff with this, just use the basic SQL statement you want.

ColinD
  • 108,630
  • 30
  • 201
  • 202
  • 1
    long id = rS.getLong("id"); is wrong - you must use column index, instead of name (with oracle) – Fisher Jan 07 '13 at 12:12
  • @Fisher: I was most definitely using this technique (using the column name) with Oracle. Perhaps there are situations/versions/configurations with Oracle in which it does't work though. – ColinD Jan 07 '13 at 16:20