Using DBMS_LOCK.sleep
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.