8.7 Implementing PLVtab.display

Chapter 8
PLVtab: Easy Access to PL/SQL Tables

 

---

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.