[Chapter 8] 8.2 Displaying PLVtab Tables

8.2 Displaying PLVtab Tables

For each type of table, PLVtab provides a display procedure to show the contents of the table. As a result, there are nine, overloaded versions of the display procedure. The headers for each of these programs are the same, except for the datatype of the first parameter (the kind of table to be displayed).

Here, for example, is the specification of the procedure to display a date table:

   PROCEDURE display 
    (tab_in IN date_table, 
     end_in IN INTEGER,
     hdr_in IN VARCHAR2 := NULL,
     start_in IN INTEGER := 1,
     failure_threshold_in IN INTEGER := 0,
     increment_in IN INTEGER := +1);

As you can see, there are lots of parameters, and that means lots of flexibility in specifying what rows are displayed and the format of the display. Here is an explanation of the various arguments:

Argument	Description
`tab_in`	The PL/SQL table you want to display. The table type must be one of those predefined in PLVtab.
`end_in`	The last row you want displayed. This is required. Until PL/SQL Release 2.3 there is no way for PLVtab to know the total number of rows defined in the table. As you will see below, you can also specify the starting row, which defaults to 1.
`hdr_in`	The header you want displayed before the individual rows are written out using the `p.l` procedure.
`start_in`	The first row you want displayed. The default value is 1. This is placed after the `end_in` argument because in almost every case it will not need to be specified.
`failure_threshold_in`	The number of times the display program can reference an undefined row in the table before it stops trying any more. Remember: PL/SQL tables are sparse. Consecutive rows do not need to be defined, but the display program does need to move sequentially through the table to display its rows.
`increment_in`	The increment used to move from the current row to the next row. The default value is 1, but you could ask display to show every fifth row by passing a value of 5.

The following examples illustrate how the different arguments are used.

8.2.1 Displaying Wrapped Text

The display_wrap program of the PLVprs package takes advantage of the PLVtab package in several ways. It declares and uses a VARCHAR2(2000) table to receive the output from the wrap procedure, which wraps a long string into multiple lines, each line of which is stored in a row in the PL/SQL table. This table is then displayed with a call to the display procedure. Notice that display_wrap also turns off the PLVtab header and sets the prefix before performing the display. These toggles for PLVtab are discussed in the next section.

PROCEDURE display_wrap
  (text_in IN VARCHAR2,
   line_length IN INTEGER := 80,
   prefix_in IN VARCHAR2 := NULL)
IS
  lines PLVtab.vc2000_table;
  line_count INTEGER := 0;
BEGIN
  PLVtab.noshow_header;
  PLVtab.set_prefix (prefix_in);
  wrap (text_in, line_length, lines, line_count);
  PLVtab.display (lines, line_count); 
END;

Notice that in this call to display I employ most of the defaults: a NULL header, a starting row of 1, a failure threshold of 0 (all rows should be defined), and an increment of 1. I do not want a header since I am essentially using display as a utility within another program.

8.2.2 Displaying Selected Companies

Suppose that I have populated a PL/SQL table with company names, where the row number is the primary key or company ID. I am, therefore, not filling the PL/SQL table sequentially. By keeping track of the lowest and highest row used in the table, however, I can still display all the defined rows in the PL/SQL table as shown below.

First, the package containing the data structures associated with the list of company names:

PACKAGE comp_names
IS
   /* The table of names. */
   list PLVtab.vc80_table;
   /* The lowest row number used. */
   lo_row BINARY_INTEGER := NULL;
   /* The highest row number used. */
   hi_row BINARY_INTEGER := NULL;
END comp_names;

Then various programs have been called to fill up the PL/SQL table with any number of company names. The following call to display will show all defined rows regardless of how many there are, and how many undefined rows lie between company names.

PLVtab.display 
  (comp_names.list,
   comp_names.hi_row,
   'Selected Company Names',
   comp_names.lo_row,
   comp_names.hi_row - comp_names.lo_row);

Let's look at a concrete example. Row 1506 is assigned the value of ACME, while row 20200 contains the company name ArtForms. I can then make the above call to PLVtab.display and get the following results displayed on the screen:

Selected Company Names
ACME
ArtForms

You will probably be surprised to hear that it took more than 83 seconds on my Pentium 90Mhz laptop to produce these results. Why so long a delay? The display procedure displayed row 1506 and then attempted unsuccessfully 18,693 times to retrieve the rows between 1506 and 20200. Each time display referenced an undefined row, the PL/SQL runtime engine raised the NO_DATA_FOUND exception, which was ignored.

The conclusion you should draw from this example is that PLVtab.display does a great job of hiding these kinds of details, but it is still important for you to understand the architecture of PL/SQL tables. This understanding will help you explain what would otherwise be an absurdly slow response time -- and also help you decide when to take advantage of the PLVtab.display procedure. If your defined rows are dispersed widely, PLVtab.display may not be efficient enough a method to display the contents of your table.


8.1 Using PLVtab-Based PL/SQL Table Types		8.3 Showing Header Toggle