Re: PLSQL Collection

From: <andrewst_at_onetel.com>
Date: 14 Jun 2005 04:59:13 -0700
Message-ID: <1118750353.592510.28930@g47g2000cwa.googlegroups.com>

simon wrote:
> I am new to PLSQL.
>
> I declared an index by table:
>
> DECLARE
> TYPE index_by_type IS TABLE OF NUMBER
> INDEX BY BINARY_INTEGER;
> index_by_table index_by_type;
> BEGIN
> index_by_table(12):=...;
> index_by_table(2):=...;
> index_by_table(6):=...;
> index_by_table(8):=...;
> index_by_table.trim(2); /*so, which 2 are trimmed?*/
> END;
>
> Now which elements are trimmed by the .trim() method? The method remove n
> elements from the end of the table. But how elements are sorted and which
> are considered the last n elements? Is it by ascending order of the index
> or by order in which the elements are created?

Why didn't you try it and see?

The first thing you will find is that TRIM is not allowed with INDEX BY.

If you correct for that then the table is no longer sparse, and so you will see this:

SQL> DECLARE

  2      TYPE index_by_type IS TABLE OF NUMBER;
  3      index_by_table index_by_type := index_by_type();
  4  BEGIN
  5      index_by_table.extend(12);
  6      index_by_table(12):=12;
  7      index_by_table(2):=2;
  8      index_by_table(6):=6;
  9      index_by_table(8):=8;
 10      index_by_table.trim(2); /*so, which 2 are trimmed?*/
 11      for i in 1..12 loop
 12        dbms_output.put_line(i || ' - ' || index_by_table(i));
 13      end loop;

 14 END;
 15 /

1 -
2 - 2
3 -
4 -
5 -
6 - 6
7 -
8 - 8
9 -

10 -
DECLARE
*
ERROR at line 1:
ORA-06533: Subscript beyond count
ORA-06512: at line 12 Received on Tue Jun 14 2005 - 06:59:13 CDT