有什么办法可以跟踪由oracle函数插入数据库中的行 [英] Is there any way to keep a track of rows inserted by a oracle function in database

问题描述

我有一个程序可以从开始日期到结束日期更新余额，并且 我也想跟踪插入的记录数.我正在使用dbms_output.put_line来获取插入的记录数，但它不提供任何输出，执行完成后将显示计数的输出.程序代码如下:

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;

现在，我正在尝试从此过程中打印totalCount，以获取在此表avail_bal中插入的行数.但是没有输出. 请帮助我，在此先谢谢

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

推荐答案

这是dbms_output的工作方式，它在运行完成后显示其所有输出，您无法实时监视它.

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.

此过程的示例:

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

这篇关于有什么办法可以跟踪由oracle函数插入数据库中的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！