#

| Chapter 12 PLVio: Reading and Writing PL/SQL Source Code |

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

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

2. 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?

12.5 Managing the Target Repository |  | 12.7 Writing to the Target

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