ORA-29285: file write error with utl_file.open

CREATE OR REPLACE DIRECTORY INT_AUDIT_LOG AS '/download/dpdump/audit_logs';

CREATE OR REPLACE PROCEDURE int_log_export (

v_date_greater_than IN VARCHAR2,

v_date_less_than IN VARCHAR2,

v_filename IN VARCHAR2

)

IS

v_location VARCHAR2(400) := 'INT_AUDIT_LOG';

fid UTL_FILE.FILE_TYPE := UTL_FILE.FOPEN (v_location, v_filename , 'W');

v_line VARCHAR2(8000);

BEGIN

FOR rec IN (select AUDITLOGITEMID,AUDITLOGID,CREATED,EVENTNAME,REFERENCETYPE,REFERENCE, DATA from report.AUDIT_LOG where CREATED>=to_date(v_date_greater_than,'YYYYMMDD')

and CREATED<to_date(v_date_less_than,'YYYYMMDD'))

LOOP

v_line :=

rec.AUDITLOGITEMID || '|' ||

rec.AUDITLOGID || '|' ||

TO_CHAR (rec.CREATED, 'YYYY.MM/DD HH24:MI:SS') || '|' ||

rec.EVENTNAME || '|' ||

rec.REFERENCETYPE || '|' ||

rec.REFERENCE || '|' ||

rec.DATA;

UTL_FILE.PUT_LINE (fid, v_line);

END LOOP;

UTL_FILE.FCLOSE (fid);

EXCEPTION

WHEN OTHERS THEN UTL_FILE.FCLOSE (fid);

END;

/

Execute rep.int_log_export('20150316','20150317','20150316.txt');

ORA-29285: file write error

ORA-06512: at "SYS.UTL_FILE", line 148

This error appear when output string in a line is very long. The FOPEN max_linesize parameter must be a number in the range 1 and 32767. If unspecified, Oracle supplies a default value of 1024. Recommendation is, use "max_linesize => 32767" for each utl_file.fopen. In the above case I have added the maximum value to overcome the error.

fid UTL_FILE.FILE_TYPE := UTL_FILE.FOPEN (v_location, v_filename , 'W', 32767);