Is there any way to keep a track of rows inserted

Question

I have a procedure to update the balance from start date to end date and also I want to keep a track of number of records being inserted . I am using dbms_output.put_line to get the number of records inserted but it does not give any output , when the execution completes then the output of the count is being displayed. The code of procedure is as follows :

create or replace function updatebal(start_date IN DATE, end_date IN DATE)
RETURN NUMBER 
IS
difference number;
curr_r  number;
BEGIN 
difference := end_date - start_date;
curr_r := 0;
while curr_r <= difference LOOP
curr_r := curr_r + 10;
for curr_in in 1..10 LOOP
date_value := date_value +1 ;
insertAvailBal(date_value);
commit;
select count(*) into totalCount from avail_bal;
dbms_output.put_line('total count' || totalCount);
end loop;
END LOOP;
RETURN 1;
END;

Now I am trying to print the totalCount from this procedure to get the number of rows inserted in this table avail_bal. But getting no output. Please help me, Thanks in Advance

Answer 1

As Tony has already answered: you can't change the behaviour of dbms_output.

The recommended way of signaling progress to the outside of a stored procedure is to use the dbms_application_info package to manage information in v$session_longops

You can even manage separate progress indicators for the outer and the inner loop. v$session_longops will even display an estimate on how long the process will take based on the average duration over time. Those estimate are pretty accurate if the runtime for each (reported) step is fairly constant.

You can enhance your function like this:

create or replace function updatebal(start_date IN DATE, end_date IN DATE)
  RETURN NUMBER 
IS
  difference number;
  curr_r  number;
  main_index binary_integer;
  sub_index  binary_integer;
  main_slno  binary_integer;
  sub_slno   binary_integer;

BEGIN 
  difference := end_date - start_date;
  curr_r := 0;

  -- initialize the module information
  dbms_application_info.set_module('updatebal', 'Calculate Balance');

  -- initialize two different "handles" for the inner and outer loop
  main_index := dbms_application_info.set_session_longops_nohint;
  sub_index := dbms_application_info.set_session_longops_nohint;

  while curr_r <= difference LOOP
    curr_r := curr_r + 10;

    -- report each outer step
    dbms_application_info.set_session_longops(rindex => main_index, 
            slno => main_slno, 
            op_name => 'main loop', 
            sofar => curr_r, 
            totalwork => difference);

    for curr_in in 1..10 LOOP

      date_value := date_value +1;

      insertAvailBal(date_value);
      commit;

      select count(*) into totalCount from avail_bal;

      -- report each inner step with the totalcount
      dbms_application_info.set_session_longops(
                      rindex => sub_index, 
                      slno => sub_slno, 
                      op_name => 'Sub Loop, totalcount'||totalcount, 
                      sofar => curr_in, totalwork => 10);

    end loop;
  END LOOP;

  RETURN 1;

  dbms_application_info.set_module(null,null);
END;
/

See the manual for more details:
https://docs.oracle.com/database/121/ARPLS/d_appinf.htm#ARPLS003

Answer 2

That is how dbms_output works, it displays all its output after the run completes, you cannot monitor it in real time.

If you really need this real-time monitoring of progress, you could use a procedure with an autonomous transaction to insert the messages into a special table, and then from another session you could view the contents of that table while the process is still running.

Example of such a procedure:

procedure log_message (p_message varchar2) is
   pragma autonomous_transaction;
begin
   insert into message_table (message) values (p_message);
   commit;
end;