#

| Chapter 13 PLVfile: Reading and Writing Operating System Files |

## 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.

1. Confirm that the role assigned to this user is SUPERVISOR.

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

2. Find the second occurrence of `DELETE' starting with the fifth line.

   >     v_line := PLVfile.line ('commands.dat', 'delete', 2, 5);

3. 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

Copyright (c) 2000 O'Reilly & Associates. All rights reserved.