Pl sql updating current record in cursor
they have a bug in their code, they need to correct such a bug and understand when to actually use "for update". Tom, I issue a command : LOCK TABLE a IN ROW SHARE MODE NOWAIT; How do I unlock the table ? I searched for UNLOCK TABLE or RELEASE TABLE commands but to no avail.
Thanks for your help March 24, 2004 - am UTC commit; rollback; thats the only way to release locks gained by SQL like that.
Hi Tom, I have a very basic question about the use of SELECT FOR UPDATE cursors especially when used with DELETE statements.
Could you please explain to me why someone would want to get an an exclusive lock on the rows that are to be deleted ?
Actually, this update is somewhat flawed in concept, stk_flag sounds like it should be a derived column -- not a real column: create view v as select x.*, case when value = 75 then '*' end / the view is the right answer.
if that returns 0 rows, you are told that the data has been modified -- you need to requery if that returns 1 row, you have the row locked and will be able to delete it and no one else can touch it. If you do not need to inspect the data BEFORE doing the delete, you do not need this logic in general. In your explanation you refered to variant(database column), what do u mean by variant? December 19, 2002 - am UTC meaning the value varies from row to row.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 declare 2 cursor c is 3 select * from emp for update of sal nowait ; 4 i number; 5 begin 6 for c1 in c loop 7 update emp 8 set sal = main_seq.nextval 9 where current of c; 10 end loop; 11 end; 12 / PL/SQL procedure successfully completed.It does this since you read the row out at time T1 and you delete it at time T2. There are no TX (transaction) locks -- hence no rows are locked in that table.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.
The select for update forms puts on the row ensures o the row was not changed o the row will NOT be changed o the row is locked by you and you will be able to delete it. For Update and Group By cannot be used simultaneously. Tom, Just want to confirm the following : I have 4 identical (same code) processes running.