Forum Stats

  • 3,825,247 Users
  • 2,260,486 Discussions
  • 7,896,465 Comments

Discussions

update (select ... for update skip locked) as a new feature

User_AW6FU
User_AW6FU Member Posts: 5 Green Ribbon

It is known practise that update over select ... for update skip locked is possible to perform in the following way:

declare
  cursor cur is
    select * from ... 
    for update skip locked;
begin
  open cur;
  loop
    fetch cur bulk collect into ...;
    exit when ...
    
    forall rws in ...
      update t ...
  end loop;
  close cur;
end;
/


I always prefer to write one dml command instead of performing the command in loop, but following examle as update (select ... for update skip locked) does not work:

[email protected]> update (

select * from emp a

where a.empno in(

7369

,7499

,7521

,7566

,7654

,7698)

for update skip locked

) b

set b.job=b.job;


for update skip locked

*

ERROR at line 10:

ORA-00907: missing right parenthesis

It would be nice to have this as a new feature in next database version .


Thanks and best regards,

Dusan Valasek

[email protected]

Thorsten Kettner
1 votes

Active · Last Updated