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

Answer 2

DBMS_OUTPUT will only ever be displayed after the PL/SQL code has terminated and control has returned to the calling program.

Output is, as you found, buffered. When your PL/SQL code finishes, then the calling program (e.g. SQL*Plus) can go and fetch that output.

Answer 3

Insert into another table, maybe call it "MYOUTPUT".

Create the table:

create table myoutput (lineno number, outline varchar2(80));

Add this after your delete:

insert into MYOUTPUT values (i,'Progress: ' || to_char(i) || ' out of ' || to_char(f));

Then select from MYOUTPUT periodically to see progress.

select outline from myoutput order by lineno;

Bobby

Answer 4

You can use UTL_FILE to write output to an external file, as in:

DECLARE
  fh          UTL_FILE.FILE_TYPE;
  nRow_count  NUMBER := 0;
BEGIN
  fh := UTL_FILE.FOPEN('DIRECTORY_NAME', 'some_file.txt', 'w');

  FOR aRow IN (SELECT *
                 FROM SOME_TABLE)
  LOOP
    nRow_count := nRow_count + 1;

    IF nRow_count MOD 1000 = 0 THEN
      UTL_FILE.PUT_LINE(fh, 'Processing row ' || nRow_count);
      UTL_FILE.FFLUSH(fh);
    END IF;

    -- Do something useful with the data in aRow
  END LOOP;  -- aRow

  UTL_FILE.FCLOSE_ALL;  -- Close all open file handles, including
                        -- the ones I've forgotten about...
END;