My long time SQL*Plus loop doesn't print DBMS_OUTPUT.PUT_LINE output during execution

Question

I know that in order to print something on sqlplus like below:

begin
   dbms_output.put_line('Hello!'); 
end;
/

I need to call

set serveroutput on;

before that. I also know that is not needed, but I can also call

DBMS_OUTPUT.enable;

before, just in case. This is working for me.

But what if I want to keep printing the progress of a long loop? It seems impossible to me. I've tried everything to print some progress on the loop below but just doesn't work. Is there some way of doing that? I even tried to spool to a file and didn't work.

Note 1: I can't truncate or partition this table as the DBA doesn't want to help me with that, so I have to use this nasty loop...

Note 2: I've noticed that once the loop is done, the whole output is printed. Looks like oracle is buffering the output and printing everything at the end. I'm not sure how to avoid that and print on every loop iteration.

set serveroutput on;
declare
    e number;
    i number;
    nCount number;
    f number;
begin
    DBMS_OUTPUT.enable;
    dbms_output.put_line('Hello!'); 
    select count(*) into e from my_big_table  where upd_dt < to_date(sysdate-64);
    f :=trunc(e/10000)+1;
    for i in 1..f
    loop
       delete from my_big_table where upd_dt < to_date(sysdate-64) and rownum<=10000;
       commit;
       DBMS_OUTPUT.PUT_LINE('Progress: ' || to_char(i) || ' out of ' || to_char(f));
    end loop;
end;

Thank you for any answer.

Answer 1

There are 2 standard ways for such things:

1. set module and action in your session DBMS_APPLICATION_INFO.SET_MODULE:

   SQL> exec DBMS_APPLICATION_INFO.SET_MODULE('my_long_process', '1 from 100');
   
   PL/SQL procedure successfully completed.
   
   SQL> select action from v$session where module='my_long_process';
   
   ACTION
   ----------------------------------------------------------------
   1 from 100
   

2. set session_longops: DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS

   I'd recommend it in your case since that is exactly designed for long operations.
   Example on Oracle-Base.

----

PS: dbms_output,put_line saves all output in a collection (nested table) variable of dbms_output package, so you can't get it from another session and client can't get it during user call (execution). In addition to set serveroutput on you can also get the output using dbms_output.get_lines: http://orasql.org/2017/12/10/sqlplus-tips-8-dbms_output-without-serveroutput-on/

Btw, in case if you need to filter or analyze output from dbms_output, sometimes it's convenient to get output in a query, so you can use filter strings in where clause or aggregate them: https://gist.github.com/xtender/aa12b537d3884f4ba82eb37db1c93c25