If a lot of data is to be written directly from the database to a file, the following error may occur:
Error report:
ORA-29285: file write error
ORA-06512: at "SYS.UTL_FILE", line 148
ORA-06512: at "SYS.UTL_FILE", line 403
ORA-06512: at "SYS.UTL_FILE", line 1166
ORA-06512: at line 58
29285. 00000 - "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.
The reason for this is the fact that Oracle by default has a character limit of 1024 per file line. This only needs to be raised. A maximum of 32KB is possible.
DECLARE
vClob CLOB;
vOutputfile utl_file.file_type;
vFileLoc VARCHAR2(50) := 'Con_DIR';
vFN VARCHAR2(25) := 'CycleExtract.SQL';
vOpenMode CHAR(1) := 'W'; /* Using W for testing use A for running/default */
vMaxLineSize NUMBER := 32700; /* fyi 32767 limit */
BEGIN
vOutputFile := utl_file.Fopen(vFileLoc, vFN, vOpenMode, vMaxLineSize);