dbms_lock.sleep not working when it encounters the code

I used DBMS_LOCK.sleep(50); in the middle of the code.

However, when I run the PL/SQL code. It looks like it is running the 'DBMS_LOCK.sleep` in the beginning instead of executing the code before the sleep code.

Please suggest some solution to add about 50 seconds of delay in the PL/SQL code.

1 Answer

I suspect you may be confusing "executing" with "output". For example, if I do:

begin dbms_output.put_line('Line1'); dbms_lock.sleep(30); dbms_output.put_line('Line2');
end;

then when run, I will see nothing for 30 seconds..and then see both lines come out at the end of the execution. But the "Line 1" did indeed execute before the sleep - but we only see output at the end of the execution of a block.

We can easily demonstrate that by changing what we output

SQL> set serverout on
SQL> begin 2 dbms_output.put_line('I ran at '||to_char(sysdate,'HH24:MI:SS')); 3 dbms_lock.sleep(30); 4 dbms_output.put_line('and I ran at '||to_char(sysdate,'HH24:MI:SS')); 5 end; 6 /
I ran at 10:49:54
and I ran at 10:50:24