#

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

## 12.7 Writing to the Target

The `put_line` procedure of PLVio writes text to the target repository. Its header is overloaded as follows:

>     PROCEDURE put_line
>        (string_in IN VARCHAR2, line#_in IN INTEGER := NULL);

>     PROCEDURE put_line (line_in IN line_type);

The first, "string" version of `put_line` simply bundles the text and line number into a record of type `line_type` and then calls the second, "record" version of `put_line` as shown below:

>     PROCEDURE put_line
>        (string_in IN VARCHAR2, line#_in IN INTEGER := NULL)
>     IS
>        v_line line_type;
>     BEGIN
>        v_line.text := string_in;
>        v_line.line# := line#_in;
>        put_line (v_line);
>     END;

Why do I bother with these two versions? To make the package as easy as possible to use. In many situations, you will simply want to take a string and an optional line number and throw it out into the target. You aren't dealing with the more complex aspects of PLVio and therefore have no need for a line record. In this situation, calling the "record version" of `put_line` becomes a hassle. By writing a few extra lines of code into the package itself, I relieve my users of the burden of declaring a throw-away data structure -- the `line_type` record.

If you plan to build reusable code that will really and truly be reused, you will need to make this kind of extra effort.

The `put_line` procedure (which from this point on refers to the record version) hides all of the complexity about the current target. You simply tell PLVio that you want to put a line in the target; it worries about the specific mechanics required for the current type of repository, as discussed below.

### 12.7.1 Putting to Different Repositories

If writing to a file, `put_line` calls its comrade-in-code, PLV`file.put_line`. All details are pushed down to this building block package. This lower-level layer of code helps PLVio avoid being bogged down in writing to an operating system file.

If writing to a string, `put_line` concatenates the new text onto the existing `string_repos.text_out` value, making sure to append the specified line to the current value of `string_repos.text_out` with an intervening newline character:

>     ELSIF string_target
>     THEN
>        IF string_repos.text_out IS NULL
>        THEN
>           string_repos.text_out := line_in.text;
>        ELSE
>           string_repos.text_out :=
>              string_repos.text_out ||
>              PLVchr.newline_char ||
>              line_in.text;
>        END IF;

The use of the newline character allows you to dump the contents of this string into a readable format either for display purposes or for spooling to a file.

When writing to a PL/SQL table, `put_line` assigns the value to the appropriate row in the table. If standard output is the target, `p.l` is used to display the text.

Finally, if the target is a database table, `put_line` makes use of dynamic SQL (using the builtin DBMS_SQL package and the PL/Vision PLVdyn and PLVobj packages) to insert a row in the table and then (at the interval specified by PLVcmt) possibly perform a commit as well. The program name and type that are written to the database table (see `settrg` for information on specifying the names of the columns holding this information) are taken from the PLVobj current object.

> NOTE: When you are writing to an operating system file, you must execute the `PLVio.closetrg` command to close the file before you can see any of the new information you have written to the file.

### 12.7.2 Batch Transfer of Source to Target

PLVio provides a procedure named `src2trg`; with a single line of code, this procedure copies the specified contents of the source repository to the target. The header for `src2trg` is:

>     PROCEDURE src2trg (close_in IN BOOLEAN := TRUE);

If you pass a value of TRUE in your call to `src2tg`, then `src2trg` will also close the target when it is done performing the transfer. You will almost certainly want to do this when you are writing to a file.

The `src2trg` procedure executes a loop to read through the source with `get_lines` and write to the target with `put_lines`, as the body of the procedure makes clear:

>     PROCEDURE src2trg
>     IS
>        line line_type;
>     BEGIN
>        LOOP
>           get_line (line);
>           EXIT WHEN line.eof;
>           put_line (line);
>        END LOOP;

>        IF close_in
>        THEN
>           closetrg;
>        ENDIF;
>     END;

I wrote this procedure for the `PLVhlp.show` procedure. This program needs to read all the help text from the source and transfer it to a PL/SQL table. From that point on, the `more` program displays a page's worth of text from the PL/SQL table using the `PLVio.disptrg` procedure (described in the next section).

### 12.7.3 Displaying the Target Repository

The `disptrg` procedure displays the contents of the target repository. Its header is:

>     PROCEDURE disptrg
>        (header_in IN VARCHAR2 := NULL,
>         start_in IN INTEGER := 1,
>         end_in IN INTEGER := target_row);

The first argument, `header_in`, is an optional header to describe the output. The second and third arguments, also optional, restrict the lines to be displayed. These arguments are currently used only when the target type is PL/SQL table. For all other target types, all the code found in the target will be displayed.

Let's look at an example of using `disptrg`. In the following script (stored in file `dumpemp.sql`), I employ PLVio to write information to a table and then display it. This script simply transfers employee names from `emp` to the target PL/SQL table and then displays the contents of the target. Notice that I do not use PLVio for reading from any kind of source repository. I am only using the target side of PLVio. No one says you have to employ both source and target repositories of PLVio.

>     BEGIN
>        PLVio.settrg (PLV.pstab);
>        FOR emp_rec IN
>            (SELECT ename FROM emp WHERE deptno = &1)
>        LOOP
>           PLVio.put_line (emp_rec.ename);
>        END LOOP;
>        PLVio.disptrg;
>     END;
>     /

Here is an example of the execution of `dumpemp.sql`:

>     SQL> start dumpemp 20
>     Contents of Table
>     JONES
>     FORD
>     SMITH
>     SCOTT
>     ADAMS

I could remove the first line of the script, which sets the target, and the rest of the script would work just fine. With this simple action, the script would work with whatever target has been selected outside of the script. The call to `put_line` would add a line to the target. And the final call to `PLVio.disptrg` would display the current target's contents. The utility and applicability of `disptrg` are kept distinct from the particular target.

12.6 Reading From the Source |  | 12.8 Saving and Restoring Settings

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