My Oracle Support Banner

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

Last updated on AUGUST 03, 2020

Applies to:

PL/SQL - Version 10.2.0.5 and later
Information in this document applies to any platform.
***Checked for relevance on 26-Dec-2018***

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.





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

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
Symptoms
Cause
Solution
References


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