ULT_FILE.GET_LINE raise NO_DATA_FOUND without a reason
Hi,
I have this code
V_INPUT := UTL_FILE.FOPEN (PZ_CDR_PATH, PZ_FILENAME, 'r', 3000);
WRITE_TO_LOG ('Processing file ' || PZ_FILENAME);
LOOP
BEGIN
VN_LINE := VN_LINE + 1;
UTL_FILE.GET_LINE (V_INPUT, VZ_RECORD);
WRITE_TO_LOG ('Reading line ' || VN_LINE);
EXCEPTION
WHEN NO_DATA_FOUND THEN
WRITE_TO_LOG ('EOF: ' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE,
SQLERRM, SQLCODE);
EXIT;
END;
-- Process VZ_RECORD..................................
END LOOP;
For some reason, NO_DATA_FOUND is raised even if the file is not read entirely. I have different files and this problem doesn't happen always. If I run the procedure again, for the same file, it may work or not. The files have 2000-4000 lines, and the problem appears from time to time. If some lines are skipped from a file, then the next files will have lines skipped too (all the lines from the end of the file). The number of lines skipped appears to be random. This procedure is called from a background application, which runs permanently.
It looks like it reached the end of the file, but I have VN_LINE which tells me the number of lines read.
The database is Oracle 10g Release 10.2.3.0. I have another database (another computer), and this problem does not appear even if I run the application for the same files.
The log looks like his:
........
02-SEP-09 04.45.58.01007 PM Reading line 2640
02-SEP-09 04.45.58.01955 PM Reading line 2641
EXCEPTION 02-SEP-09 04.45.58.02699 PM ORA-01403: no data found 100
EXCEPTION 02-SEP-09 04.45.58.02715 PM EOF: ORA-06512: at "SYS.UTL_FILE", line 98
ORA-06512: at "SYS.UTL_FILE", line 656
ORA-06512: at ", line 3107 -- this line is UTL_FILE.GET_LINE (V_INPUT, VZ_RECORD);
Any idea?