在循环中调用utl_file.put时出现UTL_FILE.WRITE_ERROR [英] UTL_FILE.WRITE_ERROR when calling utl_file.put in a loop

问题描述

我的PL/SQL过程中有以下代码，我在while循环中在API_XXX.put(它调用了utl_file.put)中调用了该代码. l_xmldoc是getReportXML函数的CLOB，该函数返回xml clob.

I have below code in my PL/SQL procedure, which I called in API_XXX.put(it calls utl_file.put) in a while loop. And the l_xmldoc is CLOB from a function of getReportXML, which returns the xml clob.

我编写的将xml写入文件的代码如下:

the code I write to write xml into a file is like:

l_offset := 1;
    WHILE (l_offset <= l_length)
    LOOP
    l_char := dbms_lob.substr(l_xmldoc,1,l_offset);

    IF (l_char = to_char(10))  ---I also tried if (l_char=chr(10)) but it did not work 
    THEN
        API_XXXX.new_line(API_XXX.output, 1);
    ELSE
        API_XXXX.put(fnd_API_XXX.output, l_char);
    END IF;

    l_offset := l_offset + 1;
    END LOOP;

请注意，API_XXX是我无法修改的现有软件包，该 api在put的末尾调用fflush.

Please note that the API_XXX is the existing package which I am not able to modify, and this api calls fflush in the end of put.

API_XXX.put的部分如下("WHICH"是第一个参数):

API_XXX.put's part is like below("WHICH" is the first param):

         elsif WHICH = API_XXX.OUTPUT then
           temp_file := OUT_FNAME;
           utl_file.put(F_OUT, BUFF);
           utl_file.fflush(F_OUT);

API_XXX.new_line就像(LINES是要写的行数):

API_XXX.new_line is like(LINES is the number of lines to write):

         elsif WHICH = API_XXX.OUTPUT then
           temp_file := OUT_FNAME;
           utl_file.new_line(F_OUT, LINES);
           utl_file.fflush(F_OUT);

我注意到一个问题，在客户方的while循环中，客户方面的put/new_line过程有时会出于未知原因(可能由于l_length太大(最多167465))提高UTL_FILE.WRITE_ERROR.

I notice a that the put/new_line procedure in my customer's side will sometimes raise UTL_FILE.WRITE_ERROR for unknown reason(maybe due to the l_length is too large(up to 167465)) in the while loop from my customer.

我阅读了 Oracle PL/SQL UTL_FILE.PUT缓冲 .而且我发现这是相同的原因，我的l_xmldoc确实很大，当我对其进行循环时，我发现它没有新的行终止符，因此即使我每次都出错，该缓冲区也可以达到32767.

I read Oracle PL/SQL UTL_FILE.PUT buffering . And I found that this is the same cause, my l_xmldoc is really large and when I loop it, I found that it is without a new line terminator so the buffer is up to 32767 even though I fflush every time.

因此，如何将l_xmldoc转换为带有换行符的varchar.

So, how should I convert the l_xmldoc into a varchar with new line terminator.

PS:我确认我的客户正在使用Oralce 11g

PS: I confirmed that my customer is using Oralce 11g

推荐答案

1. 发布您正在使用的Oracle版本！或者我们可以猜测一下...

1. Post the Oracle Version you are using! Or we can just guess around...

您的fflush将无法按您预期的方式运行-从文档:

Your fflush will not work as you expect - From the documentation:

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.

tbone完全正确，行TO_CHAR(10)是错误的！只需尝试SELECT TO_CHAR(10) FROM DUAL;，您将得到10，然后将其与单个字符进行比较.一个字符永远不会是"10"，因为10有两个字符！

tbone is abolutely right the line TO_CHAR(10) is wrong! Just try SELECT TO_CHAR(10) FROM DUAL; you will get 10 which you then compare to a single character. A single character will never be '10' since 10 has two characters!

您的问题很可能是XML文件太大的缓冲区溢出，但是请记住，目标系统上的其他问题也可能导致write_error，应予以处理.

Your problem is most likely a buffer-overflow with too large XML-Files, but keep in mind, also other problems on the target system can lead to write_errors, which should be handled.

解决方案

• Quick& Dirty :由于您似乎并不关心性能，因此您可以仅在每个X字节关闭文件，然后使用A重新打开该文件以进行追加.因此，只需添加到循环中即可:

  Solutions

  • Quick&Dirty: Since you don't seem to care about performance anyways you can just close the file every X byte and reopen it with A for append. So just add to the loop:

    IF MOD( l_offset, 32000 ) = 0
    THEN
      UTL_FILE.FCLOSE( f_out );
      UTL_FILE.FOPEN( out_fpath, out_fname, f_out, 'a', 32767 );
    END IF;
    

  • 使用正确的工具完成正确的工作:UTL_FILE不适合处理复杂的数据. UTL_FILE的唯一用例是用换行符分隔的小文本行.对于其他所有内容，您都应该写入RAW字节！ (这还将允许您对ENCODING(目前仅是mini-vanilly-lucky-guess)进行porper控制)

  • Use the right tool for the right job: UTL_FILE is not suited for handling complex data. The only usecase for UTL_FILE are small newline-separated lines of text. For everything else you should write RAW bytes! (Which will also allow you porper control over ENCODING, which is currently just mini-vanilly-lucky-guess)

    使用NIO-Filechannels编写Java存储过程-快速，安全，不错...但是要小心，您的程序运行速度可能快10倍！

    Write a Java-Stored-Procedure with NIO-Filechannels - fast, safe, nice... But be careful, your program might run 10 times as fast!

    这篇关于在循环中调用utl_file.put时出现UTL_FILE.WRITE_ERROR的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！