Re: How to reset a pl/sql-collection ?

Instead of using delete which works on a entry by entry basis define two occurrences of the arrary structure. Only use the one and when you are ready to reuse it set it equal to the unused one.

type t_atsr is table of r_ats
  index by binary_integer ;

t_ats       t_atsr          ;  -- alloc plsql tbl 4 new tblspc data
t_empty   t_atsr          ;  -- empty tbl 2 allow freeing mem during

later in code
t_ats := t_empty ;
dbms_session.free_unused_user_memory ;

For your use you would not do the dbms_session.free_unused_user_memory until you were about to exit the routine either normally or due to a serious error but you would want to reset it right before you repopulate it in your logic.

HTH -- Mark D Powell -- Received on Fri Sep 23 2005 - 08:29:27 CDT