oracle - 单次迭代后立即将内容写入 UTL_FILE

Question

我有 PL/SQL block ，它从表函数查询，我使用游标逐条记录地处理它，有一些业务逻辑，最后将合格记录写入文件。

要处理的记录数高达 100 万条。总处理速度约为每分钟10000条记录。(经过少量数据测试)

现在，我需要在不同的 JSP 环境中指示处理状态。

DECLARE
 vSFile   utl_file.file_type;
 vNewLine VARCHAR2(200);
 my_cursor IS SELECT * FROM MYTABLE;
 my_details my_cursor%rowtype;
BEGIN
  vSFile := utl_file.fopen('ORALOAD', file_name,'r');

  IF utl_file.is_open(vSFile) THEN
        utl_file.get_line(vSFile, vNewLine);


        OPEN my_cursor;
        LOOP
               FETCH my_cursor INTO my_details;
               EXIT WHEN sll_cur%NOTFOUND;
               -- Do processing
               utl_file.putf(logfile,'%s ',my_details);
               -- A info tht record completed!
        END LOOP;
        CLOSE logfile;
        CLOSE my_cursor;
  END IF;
EXCEPTION
WHEN OTHERS THEN
    --Error handling
END;
/

此处写入的日志信息，在进程完成之前不可用。所以，我无法追踪它完成了多远。有人可以帮我解决这个问题吗？

Answer 1

要将数据写入文件，您应该使用 FFLUSH 过程。例如:

OPEN my_cursor;
   LOOP
      FETCH my_cursor INTO my_details;
      EXIT WHEN sll_cur%NOTFOUND;
      -- Do processing
      utl_file.putf(logfile,'%s ',my_details);
      -- Call the FFLUSH proc here..And contents are available immediately.
      utl_file.FFLUSH(logfile); 
END LOOP;

来自文档:

FFLUSH physically writes pending data to the file identified by the file handle. Normally, data being written to a file is buffered. The FFLUSH procedure forces the buffered data to be written to the file. The data must be terminated with a newline character.

Flushing is useful when the file must be read while still open. For example, debugging messages can be flushed to the file so that they can be read immediately.

在 Oracle docs 中有关于 UTL_FILE 的更多信息.