PL/SQL (MOSC)

MOSC Banner

Using DBMS_LOCK.sleep

edited Mar 3, 2016 4:08AM in PL/SQL (MOSC) 5 commentsAnswered

I've this PL/SQL code

SQL> !cat i.sql

set serveroutput on size 100000

declare

  inst_nm varchar2(90) ;

  i number :=0 ;

  j number :=0 ;

  cursor c1 is select instance_name || ' '||inst_id|| ' '||host_name from gv$instance;

begin

open c1;

for i in 1..15

loop

  fetch c1 into inst_nm;

  dbms_output.put_line('Running for ' || i ||' ' ||  inst_nm);

  dbms_lock.sleep(1);

end loop;

end;

/

My aim is that oracle should print the value in inst_nm then sleep for 1 second , then again print and sleep for 1 second. This should be done for 15 times. The block executes but Oracle sleeps for 15 seconds then print the values of inst_nm 15 times instantly. There is no delay and I want 1 second delay in between two print commands. Please suggest.

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center