Oracle PL/SQL Best Practices

Name

BIP-05: Encapsulate UTL_FILE.GET_LINE to avoid propagating the NO_DATA_FOUND exception.

Synopsis

UTL_FILE.GET_LINE raises the NO_DATA_FOUND exception when it reads past the end of a file (a common and even necessary “error” when you are reading the full contents of a file).

This reliance on an exception to signal EOF results in poorly structured code. Consider the following:

BEGIN
   LOOP
      UTL_FILE.GET_LINE (file_id, l_line);
      process_line (l_line);
   END LOOP;

   ... lots of code
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      UTL_FILE.FCLOSE (file_id);
END;

The problem with this code is that the simple loop looks, for all intents and purpose, like an infinite loop. It’s impossible to tell by looking at the code what makes the loop terminate. Upon termination, be sure to close the file. This logic is implemented in the exception section, which may be far away from the loop. This physical separation of logically related code can lead to a maintenance nightmare.

Instead of using UTL_FILE.GET_LINE directly, build your own “get next line” procedure and have it return a Boolean flag indicating whether the EOF was reached.

Example

Here’s a simple substitution for UTL_FILE.GET_LINE:

CREATE OR REPLACE PROCEDURE get_next_line (
   file_in    IN       UTL_FILE.file_type,
   line_out   OUT      VARCHAR2,
   eof_out    OUT      BOOLEAN
)
IS
BEGIN
   UTL_FILE.GET_LINE (file_in, line_out);
   eof_out := FALSE;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      line_out := NULL;
      eof_out := TRUE;
END;

Using this program, the earlier block of code becomes:

BEGIN LOOP get_next_line ...