Lock timeout on oracle 8i

From: Rudy Susanto <rsusanto_at_atlas-sp.com>
Date: 23 May 2002 21:10:05 -0700
Message-ID: <1a928d0b.0205232010.45b85f49@posting.google.com>

Hi all,

I want to convert my t-sql to pl/sql, especially related to lock timeout, do i apply with the correct programming on oracle?

==> on sqlserver:

declare @ename varchar(10)

set lock_timeout 1500

select @ename = ename
from emp (rowlock,updlock)
where empno=7900
if @@error != 0 goto errhandler
print 'ename: ' + @ename
return

errhandler:
print 'Locking record(s) failed ...'
return

==> on oracle:

declare

   i number(3,0);
   v_ename varchar2(10);

   busy exception;
   pragma exception_init(busy,-54);

begin

   i := 1;
   loop

       begin
	   select ename into v_ename
           from emp
           where empno=7900 for update nowait;
	   dbms_output.put_line('ename: ' || v_ename);

       exception 
	   when busy then 
                if i < 3 then
		   dbms_lock.sleep(0.5);
		else
		   dbms_output.put_line('Locking record(s) failed ...');
                   exit;
                end if;
        end;

	i := i + 1;

   end loop;
end;

Thanks in advance,

Rudy Susanto Received on Thu May 23 2002 - 23:10:05 CDT