How to resolve ORA-29285: file write error

Last updated on May 15th, 2019 at 03:49 pm

ORA-29285: file write error is one of the error which may get while doing file handling operation in oracle database

File handling operation are creating a new file on the Operating system, updating or modifying it. This is  a quite function used in PLSQL for file manipulation

Reason and Resolutions for ORA-29285

  1. Unix /Linux File system where you are writing the file is full i,e it is 100% utilized.
df -h /u500

/u500  100 0

declare
fileHandler UTL_FILE.FILE_TYPE;
begin
fileHandler := UTL_FILE.FOPEN('/u500', 'tech', 'W');
UTL_FILE.PUT_LINE(fileHandler, 'This is the file for test');
UTL_FILE.FCLOSE(fileHandler);
end;
/

DECLARE
*
ERROR at line 1:
ORA-29285: file write error
ORA-06512: at "SYS.UTL_FILE", line 4
ORA-06512: at "SYS.UTL_FILE", line 1169
ORA-06512: at line 6

Resolution

Free up the space in /u500  and we can check again the PLSQL block

So basically  you need to clear the unnecessary files in the file system being used .Please make sure you dont delete any files where are currently in used .If you used any active files ,then space will not be released

df -h /u500

/u500  80 20

declare
fileHandler UTL_FILE.FILE_TYPE;
begin
fileHandler := UTL_FILE.FOPEN('/u500', 'tech', 'W');
UTL_FILE.PUT_LINE(fileHandler, 'This is the file for test');
UTL_FILE.FCLOSE(fileHandler);
end;
/
  2    3    4    5    6
PL/SQL procedure successfully completed.
  1. When a file is opened by FOPEN, unless a value is specified for the MAX_LINESIZE parameter, it will default to 1024. So this error also happens if you are putting more than 1024 character in the line
DECLARE
  file_name VARCHAR2(256) := 'test.lst';
  file_text VARCHAR2(100) := '1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890....<2000 character>';
  file_id UTL_FILE.file_type;
BEGIN
  file_id := UTL_FILE.fopen('/tmp', file_name, 'W');
  UTL_FILE.put_line(file_id, file_text);
  UTL_FILE.fclose(file_id);

END;
/
DECLARE
*
ERROR at line 1:
ORA-29285: file write error
ORA-06512: at "SYS.UTL_FILE", line 2
ORA-06512: at "SYS.UTL_FILE", line 1169
ORA-06512: at line 6

Resolution

We can prevent this error specifying the max line size

DECLARE
file_name VARCHAR2(256) := 'test.lst';
file_text VARCHAR2(100) := '1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890....<2000 character>';
file_id UTL_FILE.file_type;
BEGIN
file_id := UTL_FILE.fopen('/tmp', file_name, 'W',5000);
UTL_FILE.put_line(file_id, file_text);
UTL_FILE.fclose(file_id);

END;
/

2 3 4 5 6 PL/SQL procedure successfully completed.

The MAX_LINESIZE parameter can be up to 32767. If you have lines longer than 32K then the data should be written as binary.

3.  This error can occur while  Calling UTL_FILE.PUT_LINE repeatedly in a loop when writing cumulatively more than 1024  characters . The reason is incorrect Setting of ORA_NLS10 or the variable ORA_NLS10 is not set

Example

unset ORA_NLS10

sqlplus / as sysdba

shutdown immediate

startup

sqlplus "/ as sysdba"

DECLARE
file_name VARCHAR2(256) := 'test.lst';
file_text VARCHAR2(100) := '1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890';
file_id UTL_FILE.file_type;
BEGIN
file_id := UTL_FILE.fopen('/tmp', file_name, 'W');
FOR x IN 1..11 LOOP -- write 11 records
UTL_FILE.put_line(file_id, file_text);
END LOOP;

UTL_FILE.fclose(file_id);

END;

/

ORA-29285: file write error

Resolution

Please make sure ORA_NLS10 is set in oracle database and listener environment

ORACLE_SID=TEST

ORA_NLS10=< >

sqlplus / as sysdba

shutdown

immediate

startup

lsnrctl stop TEST

lsnrctl start TEST

sqlplus / as sysdba

DECLARE
file_name VARCHAR2(256) := 'test.lst';
file_text VARCHAR2(100) := '1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890';
file_id UTL_FILE.file_type;
BEGIN
file_id := UTL_FILE.fopen('/tmp', file_name, 'W');
FOR x IN 1..11 LOOP -- write 11 records
UTL_FILE.put_line(file_id, file_text);
END LOOP;

UTL_FILE.fclose(file_id);

END;

/

PL/SQL procedure successfully completed.

I hope you like this detailed post on ORA-29285. Please do like it and provide feedback

Related Articles

How to Solve ORA-29280: invalid directory path

How to solve ORA-29283 : invalid file operation

How to Solve ORA-00942 table or view does not exist

How to resolve ORA-29913 with external tables

Oracle documentation

Leave a Reply