Advanced Oracle PL/SQL Programming with Packages

13.5. Reading From a File

PLVfile offers several different ways to read information from an operating system file. The get_line procedure gets the next line from the file. The line function returns the nth line from a file. The overloaded infile functions returns the line in which a string is found. These programs are explored below.

13.5.1. Reading the Next Line

Use the get_line procedure to read the next line from a file. The header for get_line is:

PROCEDURE get_line 
   (file_in IN UTL_FILE.FILE_TYPE, line_out OUT VARCHAR2,
    eof_out OUT BOOLEAN);

You must provide a file handle (file_in); you cannot get the next line from a file by name. This means that you must already have opened the file using one of the fopen functions. The second argument of get_line (line_out) receives the string which is found on the next line. The eof_out argument is a flag which is set to TRUE if you have read past the end of the file.

When eof_out returns TRUE, line_out is set to NULL. You should not, however, test the value of line_out to determine if you are at the end of the file. The line_out argument could be set to NULL if the next line in a file is blank.

The following script (stored in the file dispfile.sql) uses get_line to read all the lines from a file and then display those lines.

DECLARE fileid UTL_FILE.FILETYPE; line PLVfile.max_line%TYPE; eof BOOLEAN; BEGIN fileid := PLVfile.fopen ('&1'); LOOP PLVfile.get_line (fileid, line, eof); EXIT WHEN eof; p.l (line); END LOOP; PLVfile.fclose ...