UTL_FILE.GET_RAW fails on long lines if the file is opened in text mode.
Today while debugging a previously developed PL/SQL package, I had to investigate an ORA-29284: file read error.
The cause was not immediately apparent, as the procedure had been reading in and parsing other files ok, and had read in this particular file just fine up until it reached a certain point.
The file being read was text rather than binary, but the code was using GET_RAW not GET_LINE since the process was not interested in line breaks. With a little investigation, it turned out that the place where the error occurred was when the procedure began trying to read from a line which was 175,000 characters long.
If you’ve used the functions in UTL_FILE, then you’ll know that functions like GET_LINE support a maximum line size and buffer size of 32767 bytes. However in this case, the call to GET_RAW was specifying a much smaller number of bytes to read. So why did it fail?
Answer: The file was being opened for text instead of byte mode.
-- Do something ...
WHEN no_data_found THEN
WHEN others THEN
-- write error log ...
If you look at the Oracle Documentation for the UTL_FILE package, their example passes a value of ‘r’, just as in the sample code shown above. According to the documentation ‘r’ is Read Text, whereas ‘rb’ is Read Bytes Mode.
That small, simple change proved to be the solution.