Repeated Calls to UTL_FILE.PUT_LINE Fails With ORA-29285 Writing Cumulatively More Than 1024 Characters
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***
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.
-- e.g. create or replace directory MYDIR as '/tmp/mydir';
file_name VARCHAR2(256) := 'mydoc.lst';
file_text VARCHAR2(100) := '1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890';
file_id := UTL_FILE.fopen('MYDIR', file_name, 'W');
FOR x IN 1..11 LOOP -- write 11 records
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.
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