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

Last updated on JULY 05, 2017

Applies to:

PL/SQL - Version 10.2.0.5 and later
Information in this document applies to any platform.
***Checked for relevance on 22-Aug-2016***

Symptoms

 

TIP: This article is not to be confused with getting the normal ORA-29285 error where a single UTL_FILE.PUT_LINE call with more than 1024 characters fails when the size parameter is not specified using FOPEN.



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.

Cause

Sign In with your My Oracle Support account

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

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms