Is there some way to get a value from the last inserted or updated row?
I am using the merge command to do an insert or an update if the row exists. I know how to get the autogenerated key after an insert but can I get the primary key if I use the merge command?
I'm using Java with JDBC and Oracle DB.
I've hit this issue using higher level persistence layers. My solution is to do an insert, then get the resulting ID and update into the row. Not optimal I realize but I have yet to find a better solution.
However, I've found that often combination of non-primary key columns will yield a unique row. If you can identity this data in the database I recommend setting an Unique constraint on the columns so they are guaranteed to be unique. Once you have another way of accessing unique rows, you can use that to do your merge.
Long shot : you could write a trigger in the database that timestamp any update or insert and put the timestamp in a new column that you will name : last modified (or something like that). Then order by this column to get the last edited line.
As APC says "Unlike INSERT the Oracle MERGE syntax does not support a RETURNING clause. Alas."
I'm afraid the simplest answer to this is to implement your business logic as a PL/SQL function that implements your business logic and returns the value you are interested in.
Breaking the process into steps in the PL/SQL to check if the row already exists shouldnt be too much slower as you should be (I presume) doing a direct lookup on a primary key (or at least a uniquely indexed column)