Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Get last ID on inserted row in Oracle DB [duplicate]

Tags:

java

oracle

jdbc

Possible Duplicate:
PLSQL JDBC: How to get last row ID?

I have problem getting ID from tables. I have two tables AJPES_TR and TR_LOG and PK from TR_LOG table is set as foreign key in AJPES_TR table.

In TR_LOG table I just write from which file data was imported and I want to link that PK into main table. In mySQL I was doing just fine with getID.last(); int j = getID.getInt(TR_LOG_ID); but now in Oracle this doesn't work anymore.

These are my PreparedStatements:

PreparedStatement insertData = 
  con.prepareStatement(
    "INSERT INTO T_AJPES_TR(rn,sSpre,reg,eno,davcna,Ime,Priimek) VALUES (?,?,?,?,?,?,?)"
  );
PreparedStatement select_file_log = 
  con.prepareStatement("SELECT * FROM T_AJPES_TR_LOG WHERE File_import = ?"
);
PreparedStatement getID = con.prepareStatement("SELECT * FROM T_AJPES_TR_LOG");
PreparedStatement insertFile = 
  con.prepareStatement(
    "INSERT INTO T_AJPES_TR_LOG(Date_import,File_import) VALUES (?,?)"
  );

In mySQL IDs were set as autoincrement.

How can I get ID value from TR_LOG and write that value in AJPES_TR table?

like image 544
Igor Avatar asked Dec 16 '10 08:12

Igor


2 Answers

If a trigger is configured to automatically set the primary key field with the next value from a sequence, then you can modify your INSERT statement as follows:

INSERT INTO table (field1, field2, field3)
  VALUES (?, ?, ?)
  RETURNING primary_key_field INTO ?

Then, add the parameter values for the INSERT, an output parameter at the end for the primary key, and execute the query.

After the query is executed, grab the value of the output parameter. It should contain the value of the primary_key_field.

like image 124
Matthew Rodatus Avatar answered Sep 20 '22 12:09

Matthew Rodatus


In Oracle for auto-increment values used sequences

Next value is SEQUENCE_NAME.NEXTVAL, last used SEQUENCE_NAME.CURRVAL

like image 44
Michael Pakhantsov Avatar answered Sep 20 '22 12:09

Michael Pakhantsov