使用UTL_FILE写超过32k的数据时报错 ORA-29285: file write error

最新推荐文章于 2024-08-07 11:51:42 发布

小丑伍陸柒

最新推荐文章于 2024-08-07 11:51:42 发布

阅读量1.4k

点赞数

分类专栏： PLSQL 文章标签： ORA-29285

本文链接：https://blog.csdn.net/weixin_39338752/article/details/90230449

版权

PLSQL 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

使用UTL_FILE写超过32k的数据时报错 ORA-29285: file write error

例子:

DECLARE

  l_directory VARCHAR2(20) := 'MYDIR';
  l_file_name VARCHAR2(20) := '1.txt';
  l_file      utl_file.file_type;
  l_clob  CLOB;
  l_len       PLS_INTEGER;
  l_pos INTEGER := 1;
  l_buffer VARCHAR2(32767);
  l_amount BINARY_INTEGER := 32760;

BEGIN

  FOR i IN 1 .. 5000
  LOOP
    l_clob := l_clob || '0123456789';
  END LOOP;
  
  l_len := dbms_lob.getlength(l_clob);

  --此处如果是W方式就会报错	ORA-29285: file write error
  l_file := utl_file.fopen(l_directory, l_file_name, 'wb', 32767);
  WHILE l_pos < l_len LOOP
    dbms_lob.READ(l_clob, l_amount, l_pos, l_buffer);
    utl_file.put_raw(l_file, utl_raw.cast_to_raw(l_buffer));
    utl_file.fflush(l_file);
    l_pos := l_pos + l_amount;
  END LOOP;
  utl_file.fclose(l_file);
EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line(SQLERRM);
END;
/

原因参考：Attempting to Write CLOB Data Larger Than 32KB Using UTL_FILE Fails With: ORA-29285 (Doc ID 358422.1)

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

dbms_lob参考