In your exception section check for "NO_DATA_FOUND", The below is clip from:OracleŽ Database PL/SQL Packages and Types Reference GET_LINE Procedure
Originally Posted by jayanta_deb
if the flat file size is zero bytes than do not proceed further.
This procedure reads text from the open file identified by the file handle and places the text in the output buffer parameter. Text is read up to, but not including, the line terminator, or up to the end of the file, or up to the end of the len parameter. It cannot exceed the max_linesize specified in FOPEN.
UTL_FILE.GET_LINE ( file IN FILE_TYPE, buffer OUT VARCHAR2, len IN PLS_INTEGER DEFAULT NULL); Parameters
Table 168-14 GET_LINE Procedure Parameters
Parameters Description file
Active file handle returned by an FOPEN call.
The file must be open for reading (mode r); otherwise an INVALID_OPERATION exception is raised.
Data buffer to receive the line read from the file.
The number of bytes read from the file. Default is NULL. If NULL, Oracle supplies the value of max_linesize.
If the line does not fit in the buffer, a VALUE_ERROR exception is raised. If no text was read due to end of file, the NO_DATA_FOUND exception is raised. If the file is opened for byte mode operations, the INVALID_OPERATION exception is raised.
Because the line terminator character is not read into the buffer, reading blank lines returns empty strings.
The maximum size of the buffer parameter is 32767 bytes unless you specify a smaller size in FOPEN.If unspecified, Oracle supplies a default value of 1024. See also "GET_LINE_NCHAR Procedure"
INVALID_FILEHANDLE INVALID_OPERATION READ_ERROR NO_DATA_FOUND VALUE_ERROR