Some people appear to be getting impatient about the lack of activity at this blog. So time to add some content to the site.
I had an interesting error message today. (Yes, I’m a techie). But the problem now is where to put it on the site?
In this blog, an article, the wiki or a forum entry? Don’t you hate it when software gives you too many choices?
So I’d love to get feedback about where to put what on this site.
Then back to the error today.
While using an utl_file based interface we got an ORA-29284: File read error.
This error will occur when you try to read from a file that:
a) doesn’t exist
b) isn’t open for reading
c) doesn’t have privileges to be read by Oracle.
After checking the logs, I realized that half of the file had been read already.
So I expected a corrupt file (or disk) or a character set issue. (Recently we saw the very rare chr(‘32894742′) !)
I could open the file with vi, and scroll all the way down without noticing anything out of the ordinary.
So I wrote a short procedure to read the file and put it in a dummy table with row numbers.
Of course this failed again. But now I could find the culprit row.
After copying the row to a temporary file, I noticed it was 1165 bytes long. So it should fit comfortably in the varchar2(2000).
When reading that temporary file, of course the dreaded ORA-29284 struck again.
This time I searched Metalink, and found note: 1026951.6 which explains that before 8.0.5 the maximum line length was 1023 bytes.
As of 8.0.5 this limitation was raised to 32.767 bytes (which I thought was the maximum line size).
But between the lines, it tells you that it is not the default. You’ll only be able to use the higher limit when using an overloaded parameter (max_linesize) to utl_file.get_line.
So when I used utl_file.get_line(‘/var/opt/oracle/……’,'dummy.csv’,'R’,2000) the file was read without any problem.
Meanwhile, I still didn’t find a note connecting the ORA-29284 to the max_linesize. But it’s good to know that the limitation is still the default.
So 2 lessons learned today:
a) Error messages are not always what they seem.
b) When the error message doesn’t make sense, look for the other out-of-the-ordinary things. And use your imagination.
In this case the search for ‘utl_file long line’ turned up the answer.
Now to find the keywords to where to put this note. 
