utl_file ora-29285

oracle 10.2.0.4          platform.:winxp

 

执行如下报错：

declare

fhandle utl_file.file_type;
  v_sql1 varchar2(30000);
    begin
      v_sql1 := 'xxxx ';
           
      fhandle := utl_file.fopen('TMP_DIRECTORY', 't.sql', 'W');  
      for REC in (select * from aa) loop
         utl_file.put_line(fhandle , aa.a||','||aa.b);
      end loop;
      utl_file.fclose(fhandle);
      if utl_file.is_open(fhandle) then      
          UTL_FILE.FCLOSE (fhandle);
      end if;

   end;

 

确认不是权限的问题。

ORA-29285 file write error

Cause: Failed to write to, flush, or close a file.

Action: Verify that the file exists, that it is accessible, and that it is open in write or append mode.

 

解决办法：

 

---------------------------------------------------------------------------------------

fhandle := utl_file.fopen('TMP_DIRECTORY', 't.sql', 'W');  

换成

fhandle := utl_file.fopen('TMP_DIRECTORY', 't.sql', 'W',32767);  

----------------------------------------------------------------------------------------

 

References:
  Write Error Occurs Using UTL_FILE Package

Problem:
========

You are using the UTL_FILE package and are getting a write error.  


Solution:
=========

Modify your UTL_FILE package so that it does not exceed the size limitation
for your Oracle Server version.

In versions 7.X.X through 8.0.5 the size limitation is 1023 characters.

In version 8.1.5 and above, the character size limit is 32767 when you use
the max linesize parameter.


Explanation:
============

This problem occurs when you exceed a size limition in your package.

The limit with UTL_FILE is on the buffer.  Even if you call UTL_FILE
multiple times within a procedure, it is still using the same buffer
and therefore restricted to the limitations of the package.  This is not
a bug but rather a documented limitation of the UTL_FILE package.

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10037372/viewspace-700351/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10037372/viewspace-700351/