Test End of File in PL*SQL with UTL_FILE

[Palmero]

Hi all,

I want to read a flat file using the UTL_FILE Oracle package. The file must be scanned until its end (EOF).
What is the test in PL*SQL for doing the following :

LOOP

UNTIL EOF(file_handle)

Thanks in advance for your answer.

Palmero.

 

[karluk]

As far as I know, you have to insert an exception handler into your code and look for the "no_data_found" condition. The exception routine can then close the file and return control to a higher level module.

 

[mesuj]

You have to use GET_LINE to read from the file right. That will automatically exit from the loop at the end of the file.
i.e
IF ( FILE_HANDLE.IS_OPEN(filename)) THEN
LOOP
GET_LINE(FILE_HANDLE , variable_x);
--> This reads line by line
--> Other operations
END LOOP;
FILE_HANDLE.FCLOSE(filename);
END IF;

 

[JFlack]

karluk's response was correct, an end of file condition will cause a no_data_found exception. mesuj's code won't quite work, because it doesn't handle the exception. Many people don't like this, so they write their own package to wrap UTL_FILE.GET_LINE and UTL_FILE.GET. My version turns GET_LINE into a function returning a BOOLEAN which is true if GET_LINE returned data, and FALSE at EOF.

FUNCTION GET_LINE (
file_handle IN UTL_FILE.FILE_TYPE,
line_out OUT VARCHAR2
) RETURN boolean;
BEGIN
UTL_FILE.GET_LINE(file_handle, line_out);
RETURN TRUE;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN FALSE;
WHEN OTHERS THEN
RAISE;
END GET_LINE;

If my version of GET_LINE is in a package called IO, then the procedure that reads c:\docs\test.txt does:

fh UTL_FILE.FILE_TYPE;
line_read VARCHAR2(80);
BEGIN
fh := UTL_FILE.FOPEN('c:\docs','test.txt','r');
WHILE IO.GET_LINE(fh,line_read) LOOP
-- do stuff with line_read
END LOOP;
UTL_FILE.FCLOSE(fh);
EXCEPTION
-- It is a good idea to handle the other exceptions that
-- UTL_FILE might raise.
END;