How to resolve ORA-29285: file write error

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

 

Reason and Resolutions for ORA-29285

  1. File system where you are writing the file is full
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

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 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

Leave a Reply