Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2004
    Posts
    12

    Unanswered: Getting the last inserted row from a table

    Can the following query return the last row inserted in mytable:

    select * from mytable where rowid = (select max(rowid) from mytable);

    I know that if there is any deletion of record in mytable, the above query will NOT return the correct result. Will it return the correct result in case there are only inserts in mytable ?

    Please help
    Last edited by jyoti_s; 11-02-04 at 02:58.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Not necessarily, no. You should never rely on it doing so.

  3. #3
    Join Date
    Jul 2004
    Posts
    12
    Is there any other way, I can know the ROWID of the record I insert?

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Yes:

    SQL> var row_id varchar2(30)
    SQL> insert into t1 values ('x','x') returning rowid into :row_id;

    1 row created.

    SQL> print row_id

    AABYjlAAFAAAB8hAAC

  5. #5
    Join Date
    Jul 2004
    Posts
    12
    Thank you very much for the answer.
    Is there any way I can do this from a Java application (instead of invoking a stored procedure)?

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Quote Originally Posted by jyoti_s
    Thank you very much for the answer.
    Is there any way I can do this from a Java application (instead of invoking a stored procedure)?
    I don't know, I don't use Java. But really, calling a stored procedure would be a better approach.

  7. #7
    Join Date
    Jul 2004
    Posts
    12
    Thank you very much. I really appreciate your fast response.
    I have worked it out. The code snippet (for anyone who would need it) is:

    Code:
    CallableStatement cstmt = connection.prepareCall("begin insert into mytable (col1, col2) values ('str1', 'str2') returning rowid into ?; end;");
    cstmt.registerOutParameter(1, Types.VARCHAR);
    cstmt.execute();
    String rowid = cstmt.getString(1);
    I have to do this for a lot of tables and didn't want to create a stored procedure for each table. The anonymous PL/SQL block solved the problem for me.

    - Jyoti

  8. #8
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    OK, but I hope in your real code you will be using bind variables for the inputs? Like:

    CallableStatement cstmt = connection.prepareCall("begin insert into mytable (col1, col2) values (?,?) returning rowid into ?; end;");

    Otherwise performance will be very poor.

  9. #9
    Join Date
    Jul 2004
    Posts
    12
    Yes, we will use bind variables.
    Thank you.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •