Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

ULT_FILE.GET_LINE raise NO_DATA_FOUND without a reason

bestbefore00Sep 15 2009 — edited Sep 17 2009
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?
This post has been answered by Sven W. on Sep 16 2009
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Oct 15 2009
Added on Sep 15 2009
16 comments
3,423 views