var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
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 ?
Last edited by jyoti_s; 11-02-04 at 02:58.
Not necessarily, no. You should never rely on it doing so.
Is there any other way, I can know the ROWID of the record I insert?
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
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.
Originally Posted by jyoti_s
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:
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.
CallableStatement cstmt = connection.prepareCall("begin insert into mytable (col1, col2) values ('str1', 'str2') returning rowid into ?; end;");
String rowid = cstmt.getString(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.
Yes, we will use bind variables.