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.

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/ — Sayan Malakshinov, Jul 25 '20 at 00:18
While your user call is executing in database, you can't get anything from it in the same session, since your client just waits when user call is finished. But I can advise you to use Oracle scheduler: you can start your long process as a one-time job and monitor it in your session using the same variants from my answer — Sayan Malakshinov, Jul 25 '20 at 14:24

score 3 · Accepted Answer · edited Aug 25 '20 at 12:55

There are 2 standard ways for such things:

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

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

I've tried the first approach as it seems very easy to implement, but was giving me an error. I've put the set_module in the loop and the select right after that so I could see the progress... Is this possible? Or do I need to open another session to query v$session? select action from v$session where module='my_long_process'; * ERROR at line 16: ORA-06550: line 16, column 4: PLS-00428: an INTO clause is expected in this SELECT statement — Arthur Accioly, Jul 25 '20 at 13:23
I can see the data from another session. That's already useful, thank you. — Arthur Accioly, Jul 25 '20 at 13:32
Hi @arthur yes, it's easier to implement, but second one gives you more data about progress, so you can analyze it even after execution — Sayan Malakshinov, Jul 25 '20 at 14:20

score 1 · Answer 2 · answered Jul 24 '20 at 22:50

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.

score 1 · Answer 3 · answered Jul 24 '20 at 22:59

1

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

answered Jul 24 '20 at 22:59

Bobby Durrett

1,223
12
19

Do you know if I can do the select from inside my loop so I can see the results as the loop goes? On the same session? – Arthur Accioly Jul 25 '20 at 13:56
No. I was thinking you would do the select in a separate session. – Bobby Durrett Jul 26 '20 at 01:41

score 0 · Answer 4 · answered Jul 24 '20 at 23:29

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;

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

4 Answers4