Ora 29285 file write error

ORA-29285: file write error is the common error while doing file handling operation.Check out this post on various reason and solution on how to solve it

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

Источник

Содержание

  1. Repeated Calls to UTL_FILE.PUT_LINE Fails With ORA-29285 Writing Cumulatively More Than 1024 Characters (Doc ID 1077034.1)
  2. Applies to:
  3. Symptoms
  4. Cause
  5. To view full details, sign in with your My Oracle Support account.
  6. Don’t have a My Oracle Support account? Click to get started!
  7. ORA-29285: file write error
  8. ORA-29285: file write error
  9. file write error
  10. How to resolve ORA-29285: file write error
  11. Reason and Resolutions for ORA-29285
  12. ORA-29285: file write error
  13. Answers
  14. ORA-29285: file write error
  15. Answers

Repeated Calls to UTL_FILE.PUT_LINE Fails With ORA-29285 Writing Cumulatively More Than 1024 Characters (Doc ID 1077034.1)

Last updated on JANUARY 30, 2022

Applies to:

Symptoms

Calling UTL_FILE.PUT_LINE repeatedly in a loop when writing cumulatively more than 1024 characters may fail with the following error:

ORA-29285: file write error

In some cases, ORA-29283: invalid file operation may also be thrown.

The environment is running Oracle 10.2 RDBMS or later and connecting from SQL*Plus client via a database listener. However this can apply to other environments

The same SQL script works successfully when run locally (BEQUEATH) from the 10.2 Database or later.

The following sample code which writes 100 characters at a time — loop 11 times to produce 1100 characters.

The solution from this note did not work as expected:

UTL_FILE.PUT_LINE Results In UTL_FILE.WRITE_ERROR Although Max Line Size Is Less Than 1023 Bytes

The Client NLS_LANG and ORA_NLS10 was set accordingly to match the Database server settings.

Cause

To view full details, sign in with your My Oracle Support account.

Don’t have a My Oracle Support account? Click to get started!

In this Document

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.

Oracle offers a comprehensive and fully integrated stack of cloud applications and platform services. For more information about Oracle (NYSE:ORCL), visit oracle.com. пїЅ Oracle | Contact and Chat | Support | Communities | Connect with us | | | | Legal Notices | Terms of Use

Источник

ORA-29285: file write error

I got ” ORA-29285: file write error ” error in Oracle database.

ORA-29285: file write error

Details of error are as follows.

file write error

This ORA-29285 error is related with the Failed to write to, flush, or close a file.

Verify that the file exists, that it is accessible, and that it is open in write or append mode.

Calling UTL_FILE.PUT_LINE repeatedly in a loop when writing cumulatively more than 1024 characters may fail with the following error:

ORA-29285: file write error

In some cases, ORA-29283: invalid file operation may also be thrown.

The environment is running Oracle 10.2 RDBMS or later and connecting from SQL*Plus client via a database listener. However this can apply to other environments

The same SQL script works successfully when run locally (BEQUEATH) from the 10.2 Database or later.

The following sample code which writes 100 characters at a time – loop 11 times to produce 1100 characters.

The solution from this note did not work as expected:

Note 255888.1 UTL_FILE.PUT_LINE Results In UTL_FILE.WRITE_ERROR Although Max Line Size Is Less Than 1023 Bytes

The Client NLS_LANG and ORA_NLS10 was set accordingly to match the Database server settings.

ORA_NLS10 must be set on both the Database AND listener process.

Bug 8412698 UTL_FILE.FFLUSH DOES NOT FLUSH THE BUFFER

Ensure the database has been started with ORA_NLS10 set. Also ensure the database listener has been restarted since the ORA_NLS10 was set.

To verify that ORA_NLS10 is set for your database + listener you can follow the details in Bug 8412698

On the Database server (use the equivalent for your Operating system)

Find the PMON process

/environ | grep NLS

If this does not return ORA_NLS10 and NLS_LANG then these have not been set when starting the database and/or listener.

Set ORA_NLS10 and restart both the database and listener.

Sometime the related directory may be full, then you need to Free up the space.

Источник

How to resolve ORA-29285: file write error

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.

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

(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

Resolution

We can prevent this error by specifying the max line size

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

Resolution

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

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

Источник

ORA-29285: file write error

We are getting this error ORA-29285: file write error while writing to a text file using utl_file.putf package.

Using fopen funtion in code to open file as below:
V_FILEHANDLE:=UTL_FILE.FOPEN(v_file_path,’ABC’,’W’, max_linesize);

Here max linesize = 32000, so as far as I think, limitation on length of records while writing the file is not an issue.
Further code is written as:
UTL_FILE.PUTF(V_FILEHANDLE, v_text1||’|’||v_text2||’|’);
UTL_FILE.NEW_LINE(V_FILEHANDLE);
UTL_FILE.FFLUSH(V_FILEHANDLE); — This is called after every 500 lines are written to the file.

We are encountering this error every now and then, and strangely this gets resolved when we re-run the program and file gets written successfully.
Can someone please help on this please?

Oracle database 11g, version: 11.1.0.7.0

Answers

964643 wrote:
Hi,

We are getting this error ORA-29285: file write error while writing to a text file using utl_file.putf package.

Using fopen funtion in code to open file as below:
V_FILEHANDLE:=UTL_FILE.FOPEN(v_file_path,’ABC’,’W’, max_linesize);

Here max linesize = 32000, so as far as I think, limitation on length of records while writing the file is not an issue.
Further code is written as:
UTL_FILE.PUTF(V_FILEHANDLE, v_text1||’|’||v_text2||’|’);
UTL_FILE.NEW_LINE(V_FILEHANDLE);
UTL_FILE.FFLUSH(V_FILEHANDLE); — This is called after every 500 lines are written to the file.

We are encountering this error every now and then, and strangely this gets resolved when we re-run the program and file gets written successfully.
Can someone please help on this please?

Yes v_file_path is the name of an oracle directory object. No problem on that front since files are getting created in this directory, but sometimes we get this file-write error. Here is complete code:

V_FILEHANDLE UTL_FILE.FILE_TYPE;
max_linesize number := 32000;

cursor c1 is
select col1, col2 from temp_tbl;

FOR c1_rec in c1 LOOP
UTL_FILE.PUTF(V_FILEHANDLE, col1||’|’||col2||’|’);
UTL_FILE.NEW_LINE(V_FILEHANDLE);

IF CEIL((c1%ROWCOUNT)/V_COMMIT_SIZE) = FLOOR((c1%ROWCOUNT)/V_COMMIT_SIZE) THEN
UTL_FILE.FFLUSH(V_FILEHANDLE);
COMMIT;
END IF;
end loop;

Here value for v_commit_size = 500, but this error is coming after writing lakhs of records in file. Can you please suggets now.

Источник

ORA-29285: file write error

Hi,
I’m working on an Oracle Database 11g Release 11.1.0.6.0 — 64bit Production With the Real Application Clusters option.

I’ve a procedure that reads gew thousands of record and write them into a file.
The procedure work almost fine except for a case for which I’m not able to identify the problem.

I got a ORA-29285: file write error after 5774 records written into the file.
The record that is causing the error is:
«671»,»649569004″,»22457″,»22457″,»»,»»,»20110426110351″,»20110426110351″,»9=1″,»1″;»102;»,»1228″,»1″,»MM2:080518DE5005B126000176018D01BE0272018601B60276018D01BA0291018201BA021602570187028D017201A2026A019501CD02D600C001E8024F01A101E10291016E01A20272018D01C10257019D01D1026A019501C1024A00F3011703EA00AD01000386017E01B602C4016A01AE020501AD01EC02D200BC0108032401B001CD02D0015301C9023804DE00D0015C022801A602A9010101270372019101360368025C024004FE01D001AD040602AA02410583020104C20504035704A6054902E60367045B01D203D603FD00900324045B0161031605B0014C026E043F015301D603B7002801AB0351008901A0038000B0012F03C600AD01C5020D0134018302AB000D011202D6FFBF007201C8FE0700AF0021FE64FFFDFF8DFD7100360094FD0B00FBFE15FEF9FFCCFE73FE8BFFE4FEB1FE70FFFBFEFFFE80FF55FF3DFF93FF6CFF45FF3AFF74FF3AFF1EFF45FF64FFF7FE13FF1700FBFE32FF800080FF61FFE6001B009FFF18018800DDFFF9002801F5FFF900CC018400510B4B0C0B19AF08C004D40A6614E50B2A00EDFF82FE0501C0014200DE0098004203E3019504D603180C8201E6005402A101D4013E0399017601E5020A02950188033801DF014C021C0162013D028901A901E1026E0182019A02B801DF0142035F017A0183026E0186019E02620153019E0266017E01B202B401BC011B037A019501D10272017A01BA023F016E018B024701620183027E018901C9029501AD01E5028901A501E8025F0172019A024F01620183025701760197029101A501DD028601A101D10286019901D1025B01720197025701720193025B017A01A20276018D01BE0282019901C9024041″,

Could there be any problem with the length?
this record is almost 1000 chars more then the others.

Thanks in advance,
Samuel

Answers

Pelase see below which could be helpful for your issue:
UTL_FILE.PUT_LINE Fails With ORA-29285 Writing More Than 1024 Characters [ID 1077034.1]
OERR: ORA-29285 file write error [ID 194783.1]

I guess that is the problem as I founded:

This version of FOPEN does not take a parameter for the maximum line size. Thus, the default (which is 1023 on most systems) is used. To specify a different maximum line size, use the other, overloaded version of «FOPEN function».

I changed the UTL_FILE.FOPEN call this way:

and now it works.

It could be reason for this error. Please also see:
Top Articles On Using UTL_FILE Package to Perform File I/O (UNIX) [ID 44307.1]

Источник

Источник

May 5, 2021

I got ” ORA-29285: file write error ”  error in Oracle database.

ORA-29285: file write error

Details of error are as follows.

ORA-29285 - 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.

file write error

This ORA-29285 error is related with the Failed to write to, flush, or close a file.

Verify that the file exists, that it is accessible, and that it is open in write or append mode.

Calling UTL_FILE.PUT_LINE repeatedly in a loop when writing cumulatively more than 1024  characters may fail with the following error:

ORA-29285: file write error

In some cases, ORA-29283:  invalid file operation may also be thrown.

The environment is running Oracle 10.2  RDBMS or later and connecting from SQL*Plus client via a database listener. However this can apply to other environments

The same SQL script works successfully when run locally (BEQUEATH) from the 10.2 Database or later.

For Example:

The following sample code which writes 100 characters at a time – loop 11 times to produce 1100 characters.

-- create a DIRECTORY Object 'MYDIR'
-- e.g. create or replace directory MYDIR as '/tmp/mydir';
--
DECLARE
  file_name VARCHAR2(256) := 'mydoc.lst';
  file_text VARCHAR2(100) := '1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890';
  file_id UTL_FILE.file_type;
BEGIN
  file_id := UTL_FILE.fopen('MYDIR', 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;
/

The solution from this note did not work as expected:

Note 255888.1 UTL_FILE.PUT_LINE Results In UTL_FILE.WRITE_ERROR Although Max Line Size Is Less Than 1023 Bytes

The Client NLS_LANG and ORA_NLS10 was set accordingly to match the Database server settings.

ORA_NLS10 must be set on both the Database AND listener process.

Bug 8412698 UTL_FILE.FFLUSH DOES NOT FLUSH THE BUFFER

Ensure the database has been started with ORA_NLS10 set. Also ensure the database listener has been restarted since the ORA_NLS10 was set.

To verify that ORA_NLS10 is set for your database + listener you can follow the details in Bug 8412698

In Summary:

On the Database server  (use the equivalent for your Operating system)

Find the PMON process

For Linux:

$ strings /proc/<pmon process ID>/environ  | grep NLS

For Solaris:

$ pargs -e <process ID> | grep NLS

If this does not return ORA_NLS10 and NLS_LANG then these have not been set when starting the database and/or listener.

Set ORA_NLS10 and restart both the database and listener.

Sometime the related directory may be full, then you need to Free up the space.

Do you want to learn Oracle Database for Beginners, then read the following articles.

Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )

 1,382 views last month,  1 views today

About Mehmet Salih Deveci

I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress [email protected].-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  m[email protected] a mail atabilirsiniz.

Источник

I am working on a process to take the objects out of one database schema and enter any into a separate instance that cannot see one another.  I should have tracked all of my stumbling blocks as I am just getting back into Oracle from SQL Server.  But I am working towards completing the entire process within Oracle procedures.  There are “easier” ways using a combination but I am wanting to keep this entirely within Oracle as there are a lot more subject experts within Oracle versus different options that are available and easy to learn.

I am trying to open a file for append; and then write to it using UTL_FILE.  I was running without issues until this morning and I was validating 100 random tables.  I would execute and get:

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.

I knew there was something with the data size but I was writing a tiny CLOB, only 2444 characters.  After looking through the lines in the procedure and the previous successful lines and following lines this was one of the largest I was trying within this batch.

* Light Bulb *

My file when I am opening it; what am I defining the line size as? Everything needs to be defined.

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
BEGIN
vOutputfile := utl_file.Fopen(vFileloc, vFN, vOpenMode);

…

Well looks like I wasn’t defining this maximum line length.  Easy fix; default line size of UTL_FILE.FOPEN is 1024 characters / bytes.

SOLUTION:

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

…

Everything works and now I can continue!

Источник

Понравилась статья? Поделить с друзьями:

Читайте также:

  • Oracle exception raise application error
  • Ora 29278 smtp transient error 421 service not available
  • Ora 01041 внутренняя ошибка hostdef расширение не существует
  • Ora 28547 connection to server failed probable oracle net admin error
  • Oracle exception error text

    • 0 0 голоса
    Рейтинг статьи
    Подписаться
    Уведомить о
    guest

    0 комментариев
    Старые
    Новые Популярные
    Межтекстовые Отзывы
    Посмотреть все комментарии