• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
Techgoeasy

Techgoeasy

Learn Oracle, PHP, HTML,CSS,Perl,UNIX shell scripts

  • Home
  • Oracle
    • Oracle database
    • Oracle Ebusiness Suite
    • Oracle weblogic
    • Oracle Performance Tuning
    • Oracle Hyperion
    • Oracle Cloud Tutorials
  • SQL
  • interview questions
  • Linux
  • PHP and HTML
  • Downloads
  • General
Home » Oracle » How to resolve ORA-29285: file write error

How to resolve ORA-29285: file write error

February 7, 2019 by techgoeasy Leave a Comment

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

File handling operations are creating a new file on the Operating system, updating or modifying it. This function is used quite often 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 that are currently in use.If you delete 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.

(2) 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 characters 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 by 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 the 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
ORA-29280: invalid directory path
ORA-29283 : invalid file operation
ORA-00942 table or view does not exist
ORA-29913
FND_FILE in oracle apps
https://docs.oracle.com/cd/E11882_01/server.112/e17766/e29250.htm

Filed Under: Oracle, Oracle Database, Oracle Sql Tagged With: file write error, ORA-29285, ORA-errors

Reader Interactions

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Primary Sidebar



Subscribe to our mailing list

Enter your email address to subscribe to this blog and receive notifications of new posts by email

Recent Posts

  • Password Version in oracle
  • How to login as user without changing the password in Oracle database(alter user identified by values)
  • How to check encrypted tablespace in the Database
  • How To Export -Import TDE Master Encryption Key
  • How to Configure Auto Login TDE Wallet

Copyright © 2023 : TechGoEasy

  • Hire me
  • Privacy Policy
  • Contact Us
  • New? Start Here
  • About Us