You Asked
In my PL/SQL code, when I select for update, if anybody else is working & has been locked what I want to update, I want my program to wait for n seconds for the row(s) to be released, if released then continue updating, if still not released after n seconds, detects the user who is locking the resource and informs me that person. I am writing pseudo code like this: select xxx for update yyy wait n seconds message (the user USER is locking the record RECORD) Thanks.
Tom:
Your wrong. select * from update nowait will raise an ORA-54, resource busy.
That is what NOWAIT does. Try it and see.
Why use execute immediate? It is static sql. sigh....
In one session I execute:
ops$tkyte@ORA817DEV.US.ORACLE.COM> lock table emp in exclusive mode;
Table(s) Locked.
In another, I run:
ops$tkyte@ORA817DEV.US.ORACLE.COM> set echo on
ops$tkyte@ORA817DEV.US.ORACLE.COM> set timing on
ops$tkyte@ORA817DEV.US.ORACLE.COM> @test
ops$tkyte@ORA817DEV.US.ORACLE.COM> declare
2 resource_busy exception;
3 pragma exception_init( resource_busy, -54 );
4 success boolean := False;
5 l_rec emp%rowtype;
6 begin
7
8 for i in 1 .. 3
9 loop
10 exit when (success);
11 begin
12 select * into l_rec from emp where rownum=1 for update NOWAIT;
13 success := true;
14 exception
15 when resource_busy then
16 dbms_lock.sleep(1);
17 end;
18 end loop;
19
20 if ( not success ) then
21 raise_application_error( -20001, 'row is locked by another session' );
22 end if;
23 end;
24 /
declare
*
ERROR at line 1:
ORA-20001: row is locked by another session
ORA-06512: at line 21
works as advertised.
Again, in one session issue:
ops$tkyte@ORA817DEV.US.ORACLE.COM> update emp set ename=ename where empno=7369;
1 row updated.
Now, goto another session and:
ops$tkyte@ORA817DEV.US.ORACLE.COM> declare
2 resource_busy exception;
3 pragma exception_init( resource_busy, -54 );
4 success boolean := False;
5 l_rec emp%rowtype;
6 begin
7
8 for i in 1 .. 3
9 loop
10 exit when (success);
11 begin
12 select * into l_rec from emp where empno=7369 for update NOWAIT;
13 success := true;
14 exception
15 when resource_busy then
16 dbms_lock.sleep(1);
17 end;
18 end loop;
19
20 if ( not success ) then
21 raise_application_error( -20001, 'row is locked by another session' );
22 end if;
23 end;
24 /
declare
*
ERROR at line 1:
ORA-20001: row is locked by another session
ORA-06512: at line 21
again works as advertised.
If you can prove otherwise, lay out the steps bit by bit and show us -- just like I do.