Oracle PL/SQL UTL_FILE.PUT 缓冲
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7206312/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle PL/SQL UTL_FILE.PUT buffering
提问by Dustin
I'm writing a large file > 7MB from an Oracle stored procedure and the requirements are to have no line termination characters (no carriage return/line feed) at the end of each record.
我正在从 Oracle 存储过程中写入一个大于 7MB 的大文件，并且要求在每条记录的末尾没有行终止字符（没有回车/换行）。
I've written a stored procedure using UTL_FILE.PUT and I'm following each call to UTL_FILE.PUT with a UTL_FILE.FFLUSH. This procedure errors with a write error once I get to the point where I've written more than the buffer size (set to max 32767) although I'm making the FFLUSH calls. The procedure works fine if I replace the PUT calls with PUT_LINE calls.
我已经使用 UTL_FILE.PUT 编写了一个存储过程，并且我正在使用 UTL_FILE.FFLUSH 跟踪对 UTL_FILE.PUT 的每次调用。尽管我正在进行 FFLUSH 调用，但一旦我达到写入超过缓冲区大小（设置为最大 32767）的程度，此过程就会出现写入错误。如果我用 PUT_LINE 调用替换 PUT 调用，该过程工作正常。
Is it not possible to write more than the buffer size without a newline character? If so, is there a work around?
如果没有换行符，是否不能写入超过缓冲区大小的内容？如果是这样，是否有解决方法？
回答by Ollie
Dustin,
达斯汀，
The Oracle documentation here: http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/u_file.htm#i1003404
这里的 Oracle 文档：http: //download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/u_file.htm#i1003404
States that: 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.
声明： FFLUSH 将挂起的数据物理写入文件句柄标识的文件。通常，写入文件的数据会被缓冲。FFLUSH 过程强制将缓冲的数据写入文件。数据必须以换行符结束。
The last sentence being the most pertinent.
最后一句最为中肯。
Could you not write the data using UTL_FILE.PUT_LINE before then searching the resulting file for the line terminators and removing them?
在搜索结果文件中的行终止符并删除它们之前，您不能使用 UTL_FILE.PUT_LINE 写入数据吗？
Just a thought....
只是一个想法....
回答by Dave Costa
deleted quote from docs, see Ollie's answer
从文档中删除了引用，请参阅 Ollie 的回答
Another possible way to do this is a Java stored procedure, where you can use the more full-featured Java API for creating and writing to files.
另一种可能的方法是使用 Java 存储过程，您可以在其中使用功能更全面的 Java API 来创建和写入文件。
回答by Adam Paynter
Although it is less than desirable, you could always PUTuntil you have detected that you are nearing the buffer size. When this occurs, you can FCLOSEthe file handle (flushing the buffer) and re-open that same file with FOPENusing 'a'(append) as the mode. Again, this technique should generally be avoided, especially if other processes are also trying to access the file (for example: closing a file usually revokes any locks the process had placed upon it, freeing up any other processes that were trying to acquire a lock).
尽管它不太理想，但PUT在检测到接近缓冲区大小之前，您始终可以这样做。发生这种情况时，您可以使用(append) 作为模式FCLOSE处理文件句柄（刷新缓冲区）并重新打开同一个文件。同样，通常应该避免这种技术，尤其是当其他进程也试图访问该文件时（例如：关闭文件通常会撤销该进程对其施加的任何锁，释放任何其他试图获取锁的进程）。FOPEN'a'
回答by Dustin
Thanks for all the great responses, they have been very helpful. The java stored procedure looked like the way to go, but since we don't have a lot of java expertise in-house, it would be frowned upon by management. But, I was able to find a way to do this from the stored procedure. I had to open the file in write byte mode 'WB'. Then, for each record I'm writing to the file, I convert it to the RAW datatype with UTL_RAW.CAST_TO_RAW. Then use UTL_FILE.PUT_RAW to write to the file followed by any necessary FFLUSH calls to flush the buffers. The receiving system has been able to read the files; so far so good.
感谢所有伟大的回应，他们非常有帮助。Java 存储过程看起来是可行的方法，但由于我们内部没有很多 Java 专业知识，因此管理人员会不屑一顾。但是，我能够从存储过程中找到一种方法来做到这一点。我不得不以写字节模式“WB”打开文件。然后，对于我写入文件的每条记录，我使用 UTL_RAW.CAST_TO_RAW 将其转换为 RAW 数据类型。然后使用 UTL_FILE.PUT_RAW 写入文件，然后使用任何必要的 FFLUSH 调用来刷新缓冲区。接收系统已经能够读取文件；到目前为止，一切都很好。
