10.7 Clearing the PL/SQL Table

What happens when you are done with a PL/SQL table and want to remove it from memory? If a PL/SQL table is like a table, we should be able to DELETE the rows of that table or DROP it entirely, right? It's a nice idea, but you can't perform a SQL DELETE statement on a PL/SQL table because it is not stored in the database. You also cannot DROP a PL/SQL table.

You can set a single row to NULL with the following kind of assignment:

company_names_table (num_rows) := NULL;

But this assignment doesn't actually remove the row or make it undefined; it just sets the value of the row to NULL.

The only way to actually empty a PL/SQL table of all rows is to perform an aggregate assignment with a table that is empty -- a table, that is, with no rows defined.

With this approach, for every PL/SQL table you want to be able to empty, you declare a parallel, empty table of the same table type. When you are finished working with your table, simply assign the empty table to the actual table. This will unassign all the rows you have used. The following example demonstrates this technique:

DECLARE
   TYPE company_names_tabtype IS TABLE OF company.name%TYPE
      INDEX BY BINARY_INTEGER;
   company_names_tab company_names_tabtype;

   /* Here is the empty table declaration */
   empty_company_names_tab company_names_tabtype;

BEGIN
   ... set values in company names table ...

   /* The closest you can come to "dropping" a PL/SQL table */
   company_names_tab := empty_company_names_tab;

END;

NOTE: PL/SQL Release 2.3 offers a DELETE operator so that you can delete all or some rows of a PL/SQL table.