一次迭代后立即将内容写入UTL_FILE [英] Write contents into UTL_FILE immediately after a single iteration

问题描述

我有PL/SQL块，它可以通过Table函数进行查询，并使用游标按记录处理记录，具有一些业务逻辑，最后将合格记录写入文件中.

I have PL/SQL Block, it query from a Table function and I use a cursor to process it record by record, have some business logic and finally write the Qualified records into the file.

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

Number of records to be processed is upto 1 Million. And the total processing speed is roughly 10000 records per minute.(After testing with few chunks of data)

现在，我需要指出其他环境(JSP)中的处理状态.

Now, that I need to indicate the processing status in a different environment, 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;
/

在此过程完成之前，此处提供的日志信息不可用.因此，我无法追踪完成的程度. 有人可以帮我吗?

The log information written here, is not available until the completion of the process. So, I am unable to track, how far it is completed. Can someone please assist me on this?

推荐答案

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

To write data to the file you should use the FFLUSH procedure. For instance:

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物理地将待处理数据写入由 文件句柄.通常，将缓冲写入文件的数据.这 FFLUSH过程强制将缓冲的数据写入文件. 数据必须以换行符终止.

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文档中有关UTL_FILE的更多信息.

There is more information on UTL_FILE in the Oracle docs.

这篇关于一次迭代后立即将内容写入UTL_FILE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！