Oracle PL/SQL UTL_FILE.PUT 缓冲
我正在写一个大文件>来自 Oracle 存储过程的 7MB，要求每条记录的末尾没有行终止字符（没有回车/换行）。
我已经使用 UTL_FILE.PUT 编写了一个存储过程，并且我使用 UTL_FILE.FFLUSH 跟踪对 UTL_FILE.PUT 的每次调用。尽管我正在进行 FFLUSH 调用，但一旦我写入的内容超过了缓冲区大小（设置为最大 32767），此过程就会出现写入错误。如果我用 PUT_LINE 调用替换 PUT 调用，该过程可以正常工作。
是否无法在没有换行符的情况下写入超过缓冲区大小的内容？如果是这样，有解决办法吗？
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.
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.
Is it not possible to write more than the buffer size without a newline character? If so, is there a work around?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
Dustin，
Oracle 文档在这里：
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/u_file.htm#i1003404
指出：
FFLUSH 将挂起的数据物理写入由文件句柄标识的文件。通常，写入文件的数据会被缓冲。 FFLUSH 过程强制将缓冲数据写入文件。数据必须以换行符结束。
最后一句是最相关的。
在搜索结果文件中的行终止符并将其删除之前，您是否不能使用 UTL_FILE.PUT_LINE 写入数据？
只是一个想法......
Dustin,
The Oracle documentation here:
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.
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?
Just a thought....
删除了文档中的引用，请参阅 Ollie 的回答
另一种可能的方法是使用 Java 存储过程，您可以在其中使用功能更齐全的 Java API 来创建和写入文件。
deleted quote from docs, see Ollie's answer
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.
虽然这不太理想，但您始终可以
PUT直到您检测到已接近缓冲区大小。发生这种情况时，您可以FCLOSE文件句柄（刷新缓冲区）并使用'a'通过FOPEN重新打开同一文件（附加）作为模式。同样，通常应该避免这种技术，特别是当其他进程也尝试访问该文件时（例如：关闭文件通常会撤销该进程对其施加的任何锁定，从而释放试图获取锁定的任何其他进程） ）。Although it is less than desirable, you could always
PUTuntil you have detected that you are nearing the buffer size. When this occurs, you canFCLOSEthe file handle (flushing the buffer) and re-open that same file withFOPENusing'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).感谢所有的精彩回复，他们非常有帮助。 Java 存储过程看起来是可行的方法，但由于我们内部没有很多 Java 专业知识，因此管理层会不赞成这样做。但是，我能够从存储过程中找到一种方法来执行此操作。我必须以写入字节模式“WB”打开文件。然后，对于写入文件的每条记录，我使用 UTL_RAW.CAST_TO_RAW 将其转换为 RAW 数据类型。然后使用 UTL_FILE.PUT_RAW 写入文件，然后进行任何必要的 FFLUSH 调用来刷新缓冲区。接收系统已能够读取文件；到目前为止，一切都很好。
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.