Tom Could you kindly comment on the validity of the statement below: From what you told me my conclusions are: 1. For example, the above cursor can be rewritten as follows: CURSOR lock_departure(x_dep_id NUMBER) IS select departure_id from WSH_DEPARTURES where DEPARTURE_ID = x_dep_id FORUPDATE NOWAIT; CURSOR lock_deliveries(x_dep_id NUMBER) IS select delivery_id from WSH_DELIVERIES where ACTUAL_DEPARTURE_ID = x_dep_id FOR UPDATE NOWAIT; CURSOR lock_line_details(x_dep_id NUMBER) IS select line_detail_id from SO_LINE_DETAILS where DEPARTURE_ID = x_dep_id FOR UPDATE NOWAIT; CURSOR lock_picking_details(x_dep_id NUMBER) IS select picking_line_detail_id from SO_PICKING_LINE_DETAILS where DEPARTURE_ID = x_dep_id FOR UPDATE NOWAIT; Begin OPEN lock_departure(entity_id); CLOSE lock_departure; OPEN lock_deliveries(entity_id); CLOSE lock_deliveries; OPEN lock_line_details(entity_id); CLOSE lock_line_details; OPEN lock_picking_details(entity_id); CLOSE lock_picking_details; End; In summary, do not code a SQL statement that performs an unqualified lock via the FOR UPDATE clause.
The employee deletion from the transaction table is performed using the WHERE CURRENT OF clause as shown below.In this scenario, the cursor result set can be limited using the traditional When we associate a SELECT statement with more than one table joined together to a cursor with a FOR UPDATE clause, we end up locking all the tables in the FROM clause of the SELECT statement, where we just need to lock a single table for our purpose.The FOR UPDATE OF clause helps us in locking up the intended table rather all available tables.In that case, if in a given table, when my query updates one set of data, same query should be able to update different set of data by using select for update clause. declare cursor c1 is select PRS_WOO_PRCS_ID,prs_sts from prcs_sts where PRS_WOO_PRCS_ID = 'PF31' for update of prs_sts; begin for c1_rec in c1 loop update prcs_sts set prs_sts = 'Y' where current of c1; end loop; end; / Now from a different session, i run another plsql statement which updates rows with process ids PF32.declare cursor c1 is select PRS_WOO_PRCS_ID,prs_sts from prcs_sts where PRS_WOO_PRCS_ID = 'PF32' for update of prs_sts; begin for c1_rec in c1 loop update prcs_sts set prs_sts = 'Y' where current of c1; end loop; end; / However, to my surprise, the second query should be able to update the rows marked PF32 which was not happening. Thanks Sitarama Chakravarthy I can only imagine that you made a mistake somewhere in your testing. Using an autonomous transaction, I can demonstrate in a single session that this works.