Oracle PL/SQL Programming, 2nd Edition

Oracle PL/SQL Programming, 2nd EditionSearch this book
Previous: 10.6 Filling the Rows of a PL/SQL TableChapter 10
PL/SQL Tables
Next: 10.8 PL/SQL Table Enhancements in PL/SQL Release 2.3
 

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.


Previous: 10.6 Filling the Rows of a PL/SQL TableOracle PL/SQL Programming, 2nd EditionNext: 10.8 PL/SQL Table Enhancements in PL/SQL Release 2.3
10.6 Filling the Rows of a PL/SQL TableBook Index10.8 PL/SQL Table Enhancements in PL/SQL Release 2.3

The Oracle Library Navigation

Copyright (c) 2000 O'Reilly & Associates. All rights reserved.

Library Home Oracle PL/SQL Programming, 2nd. Ed. Guide to Oracle 8i Features Oracle Built-in Packages Advanced PL/SQL Programming with Packages Oracle Web Applications Oracle PL/SQL Language Pocket Reference Oracle PL/SQL Built-ins Pocket Reference