Re: Emptying a PL/SQL TABLE

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;

M.Landa Received on Mon Jul 29 1996 - 00:00:00 CEST