Practice Notes: UTL_FILE mess

It is most common for apps developer to create outbound interfaces in text files and the usual requirement will of delivering 40 - 60 columns of data. Typical choice for a developer is to use UTL_FILE package.

Crux of the mess is we have to deliver above said number of column data on the average. Usually beginners would start like this

l_utl_file UTL_FILE.FILE_TYPE;

l_utl_file := UTL_FILE.FOPEN(p_dir, p_file_name,'w');

Then goes on to write to this file. All things set, compiles successfully and when run, produces following error:

ORA-29285: file write error

What happened here? we do have permission to write to that directory but still we get this error. It happens because, on average utl file with 40 - 60 columns takes at least 1500 - 2000 characters each line with proper formatting while default for an utl file line size is around 1000. The following is the procedure declaration for FOPEN.

function fopen returns record

(

location in varchar2,

filename in varchar2,

open_mode in varchar2,

max_linesize in binary_integer default

); --courtesy from http://www.adp-gmbh.ch/ora/plsql/utl_file.html

Most references we find for FOPEN miss the fourth default parameter set to 32K of characters. Hence for a typical interface flat file, it is necessary to open the file with required line size to not to see the write error.