0

i need last id that insert into persons for some manipulation in java. I have two insert then if I use getGeneratedKeys I get “operation not allowed” error. I use a select query to retrieve current value of sequence but it cause “an into clause is expected” error. How could I manage to access to last person id. The query:

    begin
        insert into Persons  
           (Id,First_Name,Last_Name)  
           values (person_seq.nextval  ,?  ,?  );
        insert into Relations (relation_id, person_id) 
            values (relation_seq.nextval,person_seq.currval); 
        SELECT person_seq.currval FROM DUAL;
    end;

the java code:

    stmt = connectionManager.getConnection().prepareStatement(query);//,new String[] {"ID"});
    stmt.setString(1, family.getFName());
    stmt.setString(2, family.getLName());
    ResultSet resultSet = stmt.executeQuery();
    ret = resultSet.getLong(1);
Drunix
  • 3,313
  • 8
  • 28
  • 50
user4002899
  • 85
  • 1
  • 18
  • possible duplicate of [How to get a value from the last inserted row?](http://stackoverflow.com/questions/241003/how-to-get-a-value-from-the-last-inserted-row) – user1717259 Feb 10 '15 at 16:30
  • i have 2 insert in this query. then i cant use generated key and when i use Returning i got error and i cant fetch what it return. – user4002899 Feb 10 '15 at 17:20

1 Answers1

2

You can declare a variable to store the new id with RETURNING INTO clause:

declare
   new_id number; --or your id row type
begin
        insert into Persons  
           (Id,First_Name,Last_Name)  
           values (person_seq.nextval  ,?  ,?  )
           returning id into new_id;
        insert into Relations (relation_id, person_id) 
            values (relation_seq.nextval,person_seq.currval); 
end;
tilley31
  • 668
  • 13
  • 19
  • thank you for your answer. how i can access new_id in java code i use executeQuery and get error "Cannot perform fetch on a PLSQL statement: next" – user4002899 Feb 10 '15 at 17:15
  • I haven't used java for a while, but see if this answer helps you: http://stackoverflow.com/questions/1915166/how-to-get-the-insert-id-in-jdbc?lq=1 – tilley31 Feb 10 '15 at 17:26