Re: Emptying a PL/SQL TABLE

mlanda_at_vnet.ibm.com wrote:
>
> In <31FCD24C.4A76_at_cam.ac.uk>, Charles Jardine <cj10_at_cam.ac.uk> writes:
> >In the PL/SQL User's Guide and Reference (Release 2.2), on
> >p. 2-43, it says:
> >
> >> However, you can use a simple workaround to delete an entire
> >> PL/SQL table. Simply assign NULL to it, as shown in the following
> >> example:
> >
> >However, as the following transcript shows, you can't assign
> >NULL to a PL/SQL table. I guess the manual is wrong :-(
> >
> >Does anyone know if it is, in fact, possible to empty a PL/SQL
> >table?
> >
> >Connected to:
> >Oracle7 Server Release 7.2.3.0.0 - Production Release
> >PL/SQL Release 2.2.3.0.0 - Production
> >
> >SQL> DECLARE
> > 2 TYPE mytype IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
> > 3 mytable mytype;
> > 4 BEGIN
> > 5 mytable := NULL;
> > 6 END;
> > 7 /
> > mytable := NULL;
> > *
> >ERROR at line 5:
> >ORA-06550: line 5, column 14:
> >PLS-00382: expression is of wrong type
> >ORA-06550: line 5, column 3:
> >PL/SQL: Statement ignored
>
> Interesting question. In V.2.0.14 of PL/SQL (Server at 7.0.12) this type
> of assignment does not give me an error. However, it does give me an
> error in v.2.2.3 of PL/SQL (Server at 7.2x). I guess a work around would
> be to loop through the table and assign each table element to null.
> For example:
>
> -- Assign null values to pl/sql table elements
>
> cnt:= 0;
> While (Cnt < Total_Cnt_Mytable) loop
> begin
> cnt:=cnt+1;
> Mytable(Cnt):=NULL;
> end;
> end loop;
>
> M.Landa

This is what I do (Oracle RDBMS 7.1.6):

    DECLARE

      TYPE mytype IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
      empty_table mytype;
      mytable     mytype;
    BEGIN
       mytable(1) := 'hi';
       mytable(2) := 'there';
       --- enough of this
       mytable := empty_table;