ORA-29287: 最大行大小无效 ORA-06512: 在 “SYS.UTL

执行存储过程export_to_csv时报错：

SQL> begin 2 export_to_csv('MYDIR'); 3 end; 4 / begin * 第 1 行出现错误: ORA-29287: 最大行大小无效 ORA-06512: 在 "SYS.UTL_FILE", line 41 ORA-06512: 在 "SYS.UTL_FILE", line 478 ORA-06512: 在 "C##A.EXPORT_TO_CSV", line 29 ORA-06512: 在 line 2

1.查看存储过程创建时的第29行： CSV_OUTPUT := UTL_FILE.FOPEN(P_DIR, OUT_FILE_NAME, 'W', MAX_LINE);

查询得知，使用了UTL_FILE 写超过32k的数据查询会报错：

CAUSE UTL_FILE.PUT has been used to write a CLOB data of size more than 32KB without flushing the buffer. While writing into an ASCII file, the buffer will be flushed only after new line character is encountered. >If a new line character is not written before closing, then CLOSE() does this. However if the current buffer exceeds the maximum allowed for a line in an ASCII file then CLOSE() raises this write error.

SOLUTION：

Open the file in ‘wb’ mode instead of ‘w’ mode. Use UTL_FILE.PUT_RAW instead of UTL_FILE.PUT

CSV_OUTPUT := UTL_FILE.FOPEN(P_DIR, OUT_FILE_NAME, 'W', MAX_LINE); 改为

CSV_OUTPUT := UTL_FILE.FOPEN(P_DIR, OUT_FILE_NAME, 'wb', 32767);