oracle 无法使用 UTL_FILE.PUT_LINE 写入大尺寸数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22652569/
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
not able to write a big size data with UTL_FILE.PUT_LINE
提问by Ram Dutt Shukla
I created a xml which includes a big amount of data. Now I am trying to write that generated xml into a file.
我创建了一个包含大量数据的 xml。现在我试图将生成的 xml 写入文件。
Declaration:
宣言：
prodFeed_file UTL_FILE.FILE_TYPE;
prodFeed_file := UTL_FILE.FOPEN ('CSV_DIR', 'feed.xml', 'w', 32767);
WRITING INTO FILE:
写入文件：
UTL_FILE.PUT_LINE(prodFeed_file,l_xmltype.getClobVal);
UTL_FILE.FCLOSE(prodFeed_file);
If l_xmltype.getClobValreturns limited record then it is working file but if the l_xmltype.getClobValexceeds the size(almost 35 KB) it is giving an error:
如果l_xmltype.getClobVal返回有限记录，则它是工作文件，但如果l_xmltype.getClobVal超过大小（近 35 KB），则会出现错误：
ORA-06502: PL/SQL: numeric or value error
ORA-06502: PL/SQL: numeric or value error
回答by pablomatico
The UTL_FILE documentation says:
"The maximum size of the buffer parameter is 32767 bytes unless you specify a smaller size in FOPEN. "
“缓冲区参数的最大大小为 32767 字节，除非您在 FOPEN 中指定较小的大小。”
You will have to write the CLOB chunk by chunk. Something like this:
您必须逐块写入 CLOB 块。像这样的东西：
DECLARE
v_clob CLOB;
v_clob_length INTEGER;
pos INTEGER := 1;
buffer VARCHAR2(32767);
amount BINARY_INTEGER := 32760;
prodFeed_file utl_file.file_type;
BEGIN
prodFeed_file := UTL_FILE.FOPEN ('CSV_DIR', 'productFeedLargo.xml', 'w', 32767);
v_clob := l_xmltype.getClobVal;
v_clob_length := length(v_clob);
WHILE pos < v_clob_length LOOP
dbms_lob.read(v_clob, amount, pos, buffer);
utl_file.put(prodFeed_file , char_buffer);
utl_file.fflush(prodFeed_file);
pos := pos + amount;
END LOOP;
utl_file.fclose(prodFeed_file);
END;
/
