12.7.1 Putting to Different Repositories

If writing to a file, put_line calls its comrade-in-code, PLVfile.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.