Avoiding memory problems with PL/SQL tables

An feature of PL/SQL tables is the ability to remove all records from the table using the TABLE_NAME.DELETE attribute. However, if you have had (say) 10,000 records in the table, then whilst the DELETE attribute will remove these entries, it does not free up the memory that was occupied by them.

The solution is to assign an empty table to reclaim the space.

declare
type mytable_type is
  table of varchar2(100)
  index by binary_integer;

the_table   mytable_type;

empty_table mytable_type;

begin
...
...
... lots of stuff with 'the_table'
...
the_table := empty_table;
-- this will free up the memory
end;