[Chapter 13] 13.5 Reading From a File

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 (fileid);
END;
/

I use the max_line variable of PLVfile to declare the line datatype. This gives me a way to avoid having to hard-code the length of a line. Then I open the file (provided through a SQL*Plus substitution parameter) in the simplest possible way: location and name combined, assuming read-only access. My simple loop reads the next line and exits when the end-of-file condition is reached. If I did retrieve a line, I display it. When done, I close the file.

13.5.2 Reading the nth Line

Use the line function to retrieve the specified line from a file. The header for line is:

FUNCTION line (file_in IN VARCHAR2, line_num_in IN INTEGER)
RETURN VARCHAR2;

Notice that in this function you supply a file name and not a file handle (in fact, you don't even have the option of providing the location and name separately). The second argument is the line number you want retrieved.

The line function opens (in read-only mode), scans, and closes your file. You do not have to -- and should not -- perform any of these steps. If the line number specified is 0 or is greater than the number of lines in the file, the function will return a NULL value.

This function is handy when the lines in your file have a predefined or predictable structure. For example, you might have an .ini or initialization file for a program in which the first line is the name of the program, the second line the date and time of last use, and the third line the user who last accessed account information. You could then use PLVfile.line to retrieve precisely the information you needed. The following call to the line function extracts just the date and time of last use. It assumes that you have also made use of the standard PL/Vision date mask when writing this information to the file.

v_lastuse := TO_DATE (PLVfile.line ('oe.ini', 2), PLV.datemask);

13.5.3 The INSTR of PLVFile

PLVfile provides a function which operates within a file in much the same way that the builtin INSTR function operates on a string. INSTR returns the position in which the n th occurrence of a substring is found. PLVfile.infile returns the line number in which the nth occurrence of a string occurs. The header of the infile function, again overloaded to allow specification of the file in two different ways, is shown below:

FUNCTION infile 
   (loc_in IN VARCHAR2,
    file_in IN VARCHAR2, 
    text_in IN VARCHAR2, 
    nth_in IN INTEGER := 1,
    start_line_in IN INTEGER := 1,
    end_line_in IN INTEGER := NULL,
    ignore_case_in IN BOOLEAN := TRUE)
RETURN INTEGER;

FUNCTION infile 
   (file_in IN VARCHAR2, 
    text_in IN VARCHAR2, 
    nth_in IN INTEGER := 1,
    start_line_in IN INTEGER := 1,
    end_line_in IN INTEGER := NULL,
    ignore_case_in IN BOOLEAN := TRUE)
RETURN INTEGER;

The arguments to the infile function are described below:

Parameter Name	Description
file_in loc_in, file_in	The name of the file to be opened. The function is overloaded to allow both the location name and combined name specification for the file. All other arguments are common among the two.
text_in	The chunk of text to be searched for in each line of the file.
nth_in	The number of times the text should be found in distinct lines in the file before the function returns the line number. Default is 1, which means the first match. This value must be at least 1.
start_line_in	The first line in the file from which the function should start its search. This value must be greater than 0.
end_line_in	The last line in the file to which the function should continue its search. If NULL (the default), then search through end of the file. This value must be greater than or equal to `start_line_in`.
ignore_case_in	Indicates whether the case of the file contents and `text_in` should be ignored when checking for its presence in the line.

The infile function opens (in read-only mode), scans, and closes your file. You do not have to -- and should not -- perform any of these steps.

The only required parameters are file_in and text_in. I can, as a result, call infile with this minimum number of arguments:

first_find := PLVfile.infile ('names.vp', 'Hanubi';

I can, however, also do so much more, as shown in the examples below.

Confirm that the role assigned to this user is SUPERVISOR.

IF PLVfile.line ('config.usr', 'ROLE=SUPERVISOR') > 0
THEN
   update_schedule;
END IF;

Find the second occurrence of `DELETE' starting with the fifth line.
```
v_line := PLVfile.line ('commands.dat', 'delete', 2, 5);
```
Verify that the third line contains a terminal type specification. I ask for an exact match on the case of the text in the file, since the setup file has a specific structure.
```
v_line := PLVfile.line
   ('setup.cfg', 'termtype=', 1, 3, ignore_case_in => FALSE);
```

The infile function differs from INSTR in at least one way: it does not support negative values for the starting line number of the search. INSTR does recognize this kind of argument, causing it to scan backwards through the string. You cannot scan backwards through the contents of a file.

13.5.3.1 Building utilities around infile

Suppose I receive profit-and-loss statements electronically from each of my regional offices every month. The number of items in the statement can change, but the file must contain a monthly total line in the format:

month_total=NNNNNN

where NNNNNN is the dollar amount.

Before the availability of UTL_FILE, you would have had to use SQL*Loader to load the file into a "temporary" table and then query the contents of that table. With UTL_FILE and (more to the point of this chapter, PLVfile's functions) you can skip the temporary table and extract the information directly from the file.

The mth_total function shown below (stored in file use\mthtotal.sf ) makes use of both PLVfile.line and PLVfile.infile to extract the monthly total for the specified region and month.

FUNCTION mth_total 
   (region_in IN INTEGER,
    month_in IN VARCHAR2,
    key_in IN VARCHAR2 := 'month_total=') RETURN NUMBER
IS
   v_file VARCHAR2(100) := 
      'pnl' || TO_CHAR (region_in) || '.' || month_in;
   v_linenum INTEGER;
   v_line PLVfile.max_line%TYPE;

   retval NUMBER := NULL;
BEGIN
   v_linenum := PLVfile.line (v_file, key_in);
   If v_linenum IS NOT NULL
   THEN
      v_line := PLVfile.line (v_file, v_linenum);
      retval := SUBSTR (v_line, LENGTH (key_in) + 1); 
   END IF;
   RETURN retval
EXCEPTION
   WHEN OTHERS THEN RETURN NULL;
END;

This function, first of all, assumes that the default directory for the profit-and-loss files has already been set. It then constructs the file name from the region number and month string (the month string is an extension of the form MMYY). The call to PLVfile.line locates the line containing the keyword (which is also passed in as an argument to increase the flexibility of the function). If the line number if not NULL, a match was found. So PLVfile.line is then called to return the text of that line. Finally, I use SUBSTR to extract only the numeric part of the line.


13.4 Opening and Closing Files		13.6 Writing to a File