Is there any way to keep a track of rows inserted by a oracle function in database

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

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;