Getting last inserted id in Oracle using JDBC

Question

I am trying to get the last generated auto increment id in JSP, something like PHP mysql_insert_id() or MySQL last_insert_id() using the getGeneratedKeys() method as mentioned in this question (I use Oracle 10g, though).

I have a TRANSPORTER table in an Oracle database that has a column named TRANSPORTER_ID type NUMBER(35, 0) that maps to the BigDecimal type in Java, and this is the primary key generated by the sequence.

 Connection con; ResultSet rs; PreparedStatement ps; String url = "jdbc:oracle:thin:@localhost:1521:xe"; String user = "root"; String pwd = "root"; Class.forName("oracle.jdbc.OracleDriver").newInstance(); con = DriverManager.getConnection(url, user, pwd); ps = con.prepareStatement("INSERT INTO transporter(transporter_name, transporter_website)VALUES(?, ?)"); ps.setString(1, "New"); ps.setString(2, "New Website"); ps.executeUpdate(); BigDecimal id = new BigDecimal(0); rs = ps.getGeneratedKeys(); while (rs.next()) { id = rs.getBigDecimal("transporter_id"); } out.println("The generated id is : " + id); 

After performing the insert operation, the code tries to call this method rs = ps.getGeneratedKeys(); above the previous while , and it failed with the following exception.

javax.servlet.ServletException: java.sql.SQLException: operation not allowed

I tried with the Oracle JDBC Driver version - 10.2.0.5.0 , and when it failed, I downloaded the higher version, which is 11.2.0.3.0 , but to no avail. What could be the reason?

Answer 1

I found some documentation about this. It's at 11g, but the situation probably won't be better for 10g.

The proximal cause of your error is probably a limitation that:

You need to access the ResultSet returned from the getGeneratedKeys method only by position

The Oracle driver also seems to require you to define a key column so that it can get the key column, not just the ROWID . Sample code for this is included in the related documentation.