8.6 Emptying Tables with PLVtab

Chapter 8
PLVtab: Easy Access to PL/SQL Tables

 

---

For PL/SQL Releases 2.2 and earlier, the only way to delete all rows from a PL/SQL table (and release all associated memory) is to assign an empty table of the same TYPE to your structure. PLVtab offers the following set of empty tables to facilitate this process for PLVtab-based tables:

empty_boolean boolean_table; empty_date date_table; empty_integer integer_table; empty_number number_table; empty_vc30 vc30_table; empty_vc60 vc60_table; empty_vc80 vc80_table; empty_vc2000 vc2000_table; empty_vcmax vcmax_table; empty_ident ident_table;

It is very easy to use these empty tables (of course, they are only empty if you do not define rows in those PL/SQL tables!). The following example shows a package body that has defined within it a PL/SQL table. This table is then modified and emptied by the program units defined in that same package body.

PACKAGE BODY paid_subs IS    listcount INTEGER := 0;    namelist PLVtab.vc80_table;    PROCEDURE addsub (name_in IN VARCHAR2) IS    BEGIN       namelist (listcount + 1) := name_in;       listcount := listcount + 1;    END;    PROCEDURE clearlist IS    BEGIN       namelist := PLVtab.empty_vc80;    END; END paid_subs;

If you have PL/SQL Release 2.3, you don't have to bother with these empty tables. Instead, you can use the PL/SQL table DELETE attribute to remove the rows from the table. The following examples illustrate the power and flexibility of this syntax:

namelist.DELETE; -- Delete all rows. namelist.DELETE (5); -- Delete row 5. namelist.DELETE (5, 677); -- Delete all rows between 5 and 677.

This is obviously a much more desirable technique -- and it highlights a drawback to the PLVtab approach to emptying tables.

8.6.1 Improving the Delete Process

As explained above, to delete all the rows from a (PL/SQL Release 2.2 and earlier) PLVtab table, you would assign an empty table to that table. The problem with this approach is that it exposes the implementation of the delete process. You have to know about the empty table and also the aggregate assignment syntax. Worse, when you do upgrade to PL/SQL Release 2.3 or above, you have to go to each of these assignments and change the code in order to take advantage of the new attribute.

A much better approach would be for PLVtab to provide not the empty tables themselves, but procedures that do the emptying for you. Such a program is very simple and is shown below:

PROCEDURE empty (table_out OUT date_table) IS BEGIN    table_out := empty_date; END;

This procedure would, of course, have to be overloaded for each table TYPE. Notice that this program uses the empty table just as you would, but that detail is hidden from view. There are two advantages to this approach:

• Now when I want to empty a table, I simply call the program as shown below:

  PLVtab.empty (my_table);

  I don't have to know about the empty tables and their naming conventions. I leave that to the package.

• When my installation upgrades to PL/SQL Release 2.3, I can take immediate advantage of the DELETE operator without changing those parts of my application that empty my tables. Instead, I can simply change the implementation of the empty procedure itself. I can implement a procedure with equivalent functionality as follows:

PROCEDURE empty (table_out OUT date_table) IS BEGIN    table_out.DELETE; END;

Yet I could also enhance the empty procedures of PLVtab to take full advantage of the flexibility offered by the DELETE attribute:

PROCEDURE empty    (table_out OUT date_table,     start_in IN INTEGER := NULL,    end_in IN INTEGER := NULL)  IS BEGIN    table_out.DELETE        (NVL (start_in, table_out.FIRST),        NVL (end_in, table_out.LAST)); END;

Through careful assignment of default values for the arguments of this new implementation, all previous uses of the empty procedure would still be valid. Future uses could take advantage of the new arguments.[1]

[1] Why isn't this technique used in PLVtab? Well, at some point, I had to stop changing my code and instead write a book about it. You are, at least, now aware of the issue and can implement this approach yourself.