How to update records without invalidating cursor oracle
It wants to make sure that in the time between T1 and T2 -- no one UPDATED that row. Dear Tom, Please consider this requirement: Field_Code Value Picked_up 10 10 No 20 15 No 10 20 No All the records with "Picked_up=No" are to be selected and populated into another table with "group by" on Field_Code, i.e.
It wants to make sure you do not delete the row without having at least looked at the modified information (that would what is commonly known as a "lost update"). Field_Code Value 10 30 20 15 The Picked_up is to be updated to 'Yes'.
It also does this to ensure that when the time comes, the delete will proceed in a non-blocking fashion. If we first do a Select for Update, we might have another record coming in when we group by subsequently. Regards, ed Wrote file 1 declare 2 cursor c is 3 select * from equipment_category_lookup for update nowait ; 4 i number; 5 begin 6 for c1 in c loop 7 update equipment_category_lookup 8 set equipment_category_lookup_seq = main_seq.nextval 9 where current of c; 10 end loop; 11* end; SQL / where current of c; * ERROR at line 9: ORA-06550: line 9, column 18: PLS-00404: cursor 'C' must be declared with FOR UPDATE to use with CURRENT OF ORA-06550: line 7, column 1: PL/SQL: SQL Statement ignored SQL ed Wrote file 1 declare 2 cursor c is 3 select * from equipment_category_lookup for update ; 4 i number; 5 begin 6 for c1 in c loop 7 update equipment_category_lookup 8 set equipment_category_lookup_seq = main_seq.nextval 9 where current of c; 10 end loop; 11* end; SQL Tom, Just want to confirm the following : I have 4 identical (same code) processes running.
What are the repercussions if we don't use SELECT FOR UPDATE for updating/deleting the data ?
Also, will the performance of stored procedures without SELECT FOR UPDATEs be better than the ones with simple update/delete statements as there is no additional overhead of locking rows. Oracle forms for example will issue a SELECT for UPDATE on any row you attempt to update or delete from the GUI. Oracle_Username = 'SCOTT' / COMMIT; select (select object_name from user_objects where object_id = lock_id1) obj_name, 2 lock_type, mode_held 3 from dba_locks 4 where session_id = (select sid from v$mystat where rownum=1) 5 / OBJ_NAME LOCK_TYPE MODE_HELD ---------- -------------------------- ---------------------------------------- T1 DML Row-S (SS) That shows the name of the mode -- going to the server concepts manual: we can see that locks the table (due to the SELECT FOR UPDATE just being issued) in Row share mode -- this prevents another session from locking that table in exclusive mode but nothing else.