[Chapter 12] 12.6 Reading From the Source

12.6 Reading From the Source

The PLVio.get_line procedure is the core program for reading from the source. This header for this program is:

PROCEDURE get_line
   (line_inout IN OUT line_type,
    curr_line#_in IN INTEGER := NULL);

The first argument, line_inout , is a record of type line_type (defined in the PLVio specification). The second argument, curr_line# , provides a current line number; if that number is not NULL, it will be used to increment the line# value found in the line_inout record.

The record contains all the information about a line necessary either for PLVio activity or other actions on a line of text. The definition of the record TYPE is:

TYPE line_type IS RECORD
   (text VARCHAR2(2000) := NULL,
    len INTEGER := NULL,
    pos INTEGER := 1,
    line INTEGER := 0, /* line # in original */
    line# INTEGER := 0,	/* line # for new */
    is_blank BOOLEAN := FALSE,
    eof BOOLEAN := FALSE);

The following table explains the different fields of a line_type record:

`text`	The line of text.
`len`	The length of the line of text.
`pos`	The current position of a scan through this line.
`line`	The line number associated with this text in the source.
`line#`	The line number associated with this text in the target.
`is_blank`	TRUE if the text RTRIMS to NULL.
`eof`	TRUE if no line was placed into the record.

12.6.1 Main Steps of get_line

The get_line procedure has two main steps:

Read a line from the source repository. If reading from a database table, get_line uses the DBMS_SQL builtin package to fetch the next row and read the text and line number from the retrieved data. If reading from a file, get_line calls the PLVfile.get_line procedure. If reading from a string, get_line finds the next newline character and uses SUBSTR to extract the desired characters.
Massage the data retrieved from the repository so that the values of all record fields are set properly. Assuming that data was found (the eof field is not set to TRUE), then the following actions are taken: replace newline characters with single spaces, replace tab characters with three spaces, increment the line number (using the second argument in the call to get_line , if provided), set the pos field to 1, set is_blank to TRUE if the string is composed solely of blanks, and compute the length of the line of text.

When these two steps are completed, the newly populated record is returned to the calling program.

12.6.2 Using get_line

To give you an idea of how you can put get_line to use, consider the SQL*Plus script shown below. Stored in file inline2.sql , this program displays all the lines of code in a given program that contain the specified string.

DECLARE
   line PLVio.line_type;
BEGIN
   PLVobj.setcurr ('&1');
   PLVio.asrc (where_in => 'INSTR (text, ''&2'') > 0');
   LOOP
      PLVio.get_line (line);
      EXIT WHEN line.eof;
      p.l (line.text);
   END LOOP;
   PLVio.closesrc;
END;
/

I call PLVobj.setcurr to set the current object to the requested program. I then point the source repository to ALL_SOURCE and add an element to the WHERE clause that will find only those lines in which the INSTR on the second argument returns a nonzero location. Now I am all set to loop through the rows identified by this WHERE clause. I exit when the eof field is set to TRUE; otherwise, I display the line and then call get_line again. Finally, I close the source when I am done, freeing up the memory used to read through ALL_SOURCE.

Here is an example of output from the inline2 program:

SQL>  start inline2 b:PLVio SUBSTR
         (SUBSTR (srcrep.select_sql, 1, loc-1) ||
          SUBSTR (srcrep.select_sql, loc));
             SUBSTR (srcrep.where_clause, 1, loc-1) ||
             SUBSTR (srcrep.where_clause, loc2+cmnt_len-1);
             SUBSTR
          SUBSTR
      RETURN SUBSTR (line_in.text, pos_in);

You might compare the implementation of this functionality in inline2.sql with the approach taken in the inline.sql script. What are the differences between the two implementations? Which would you prefer to use and maintain?