#

| Chapter 8 PLVtab: Easy Access to PL/SQL Tables |

## 8.7 Implementing PLVtab.display

I faced several challenges when building the `display` procedures:

- I had to create a separate procedure for each of the different table types, but I did not want to actually have separate display engines for each table type; the code would be very cumbersome and lengthy. Yet consolidating this code would also be difficult since each display procedure drew its information from a different PL/SQL table.

- With the implementation of PL/SQL tables prior to Release 2.3 of PL/SQL, it is impossible to obtain information about the state of a PL/SQL table from the runtime engine. Instead, you must keep track of the rows that have been used or be ready to handle the NO_DATA_FOUND exception.

I took care of the code redundancy problem by creating a single internal display procedure (`idisplay`) that is called by each of the public display procedures. Here is an example of the full body of the `display` procedure for date tables:

>        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)
>        IS
>        BEGIN
>           idate := tab_in;
>           idisplay
>             (c_date, end_in, hdr_in, start_in,
>              failure_threshold_in, increment_in);
>           idate := empty_date;
>        END;

What is going on here? First, I copy the incoming table into a private PL/SQL table (`idate`). Then I display the contents of the `idate` table and not the user's table. Finally, I empty the internal PL/SQL table to minimize memory utilization. Notice that the `idate` table does not appear in the parameter list for `idisplay`. Instead, I pass in a constant, `c_date`, to indicate that `idisplay` should get the row values from the `idate` table.

I have, then, achieved my first objective: Use a single procedure to implement all of the different overloaded versions (this follows the diamond effect described in Chapter 4, Getting Started with PL/Vision). Of course, all I have really done is move the complexity down into the `idisplay` procedure. At least, however, all the complexity is concentrated into that single module.

But when you look inside the `idisplay` procedure you will find that there is actually very little complexity there either. Instead, I further buried the "how" of displaying all these different types of PL/SQL tables in another private module, `display_row`. Here is the main loop of the `idisplay` procedure:

>        WHILE in_range (current_row) AND within_threshold
>        LOOP
>           display_row
>           (type_in, failure_threshold_in, increment_in,
>           count_misses, current_row, within_threshold);
>        END LOOP;

The `display_row` procedure takes as its first argument the type of table (`c_date`, `c_number`, etc.). It then executes an extended IF statement to determine from which table the row value should be extracted. The initial portion of this IF statement is shown below. The `rowval` variable is passed to `p.l` by the `display_row` procedure.

>       IF type_in = c_boolean
>       THEN
>          rowval :=
>             PLV.boolstg (iboolean (curr_row_inout), real_null_in => TRUE);

>       ELSIF type_in = c_date
>       THEN
>          rowval := TO_CHAR (idate (curr_row_inout), PLV.datemask);

>       ELSIF type_in = c_integer
>       THEN
>          rowval := TO_CHAR (iinteger (curr_row_inout));

Take some time to review the implementation of `PLVtab.idisplay`. On the one hand, you might think that the author is a somewhat off-balance, obsessive kind of guy. You might be right. On the other hand, you might also learn some interesting techniques for code consolidation inside packages when providing highly overloaded programs to users.

8.6 Emptying Tables with PLVtab |  | 9. PLVmsg: Single-Sourcing PL/SQL Message Text

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