In my application multiple requests simultaneously read record from one table and based on that insert new record in table.
I want to execute request serially so that the second request reads the latest value inserted by the first request.
I tried to achieve this using select for update query but it lock only row to be wait for update, as I can't update existing record it got same value as previous request got.
Is it possible using Oracle locking mechanism? How?
Dude - that's what transactions are for!
Put your code into a PL/SQL stored procedure
Wrap the select/insert in a "begin tran/commit"
Don't even think about locks, if you can avoid it!