Skip to Main Content
Advanced Oracle PL/SQL Programming with Packages
book

Advanced Oracle PL/SQL Programming with Packages

by Steven Feuerstein
October 1996
Intermediate to advanced content levelIntermediate to advanced
687 pages
16h 41m
English
O'Reilly Media, Inc.
Content preview from 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, ...
Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Oracle Database 12c PL/SQL Programming

Oracle Database 12c PL/SQL Programming

Michael McLaughlin
Oracle PL/SQL for DBAs

Oracle PL/SQL for DBAs

Arup Nanda, Steven Feuerstein
Expert Oracle PL/SQL

Expert Oracle PL/SQL

Ron Hardman, Michael McLaughlin

Publisher Resources

ISBN: 1565922387Supplemental ContentCatalog PageErrata