Advanced Oracle PL/SQL Programming with Packages

8.6. Emptying Tables with PLVtab

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, ...