Quick examples into Oracle PL/SQL collections.

There are three types of Oracle Collections you can use in PL/SQL :

• Index-by-Tables : Unbounded, Hashes , Single Dimensional
• Nested-Tables : Unbounded, Array, unordered, Single dimension
• VARRAYS : Bounded, Single Dimension

Each one of these Types have its own features and limitations.

Most of them, share some common functions like EXTEND, FIRST, LAST, ..etc

These self-explaining examples; helps the reader get a first introduction into each collection type .

/*
--Index-by-Tables or PL/SQL Tables
--* Is atable of two columns, KEY and VALUE pairs.
--* The KEY is a Binary Integer.
--* The Value is whichever type you chose.
*/

-- an array of Intgers
declare
    type t_Numbers  IS TABLE OF INTEGER
        INDEX BY BINARY_INTEGER;
    v_Numbers   t_Numbers;
    v_limit Integer := 10;
BEGIN
    DBMS_OUTPUT.PUT_LINE ( 'Example: 1' );
    For v_Count IN 1..v_limit LOOP
        v_Numbers(v_Count) :=  v_Count*10;
    END LOOP;
    For v_Count IN 1..v_limit LOOP
        DBMS_OUTPUT.PUT_LINE ( v_Count||':'||v_Numbers(v_Count) );
    END LOOP;
END;

-- an array of VarChars
declare
    type t_Chars  IS TABLE OF VARCHAR(100)
        INDEX BY BINARY_INTEGER;
    v_Chars   t_Chars;
    v_limit Integer := 10;
BEGIN
    DBMS_OUTPUT.PUT_LINE ( 'Example: 2' );
    For v_Count IN 1..v_limit LOOP
        v_Chars(v_Count) :=  LPAD(to_char(v_Count),100-length(v_Count),'0');
    END LOOP;
    For v_Count IN 1..v_limit LOOP
        DBMS_OUTPUT.PUT_LINE ( v_Count||':'||v_Chars(v_Count) );
    END LOOP;
END;

-- an array of VarChars with static assignment
declare
    type t_Chars  IS TABLE OF VARCHAR(100)
        INDEX BY BINARY_INTEGER;
    v_Chars   t_Chars;
BEGIN
    DBMS_OUTPUT.PUT_LINE ( 'Example: 3' );
    v_Chars(0) := 'SWITZERLAND';
    v_Chars(1) := 'GERMANY';
    v_Chars(2) := 'NETHERLANDS';
    v_Chars(3) := 'USA';
    v_Chars(4) := 'BRAZIL';
    --  Using COUNT
    For v_Count IN 0..v_Chars.COUNT-1 LOOP
        DBMS_OUTPUT.PUT_LINE ( v_Count||':'||v_Chars(v_Count) );
    END LOOP;
    DBMS_OUTPUT.PUT_LINE ( ' ' );
    v_Chars(5) := 'AUSTRIA';
    --
    --  Using FIRST and LAST
    For v_Count IN v_Chars.FIRST..v_Chars.LAST LOOP
        DBMS_OUTPUT.PUT_LINE ( v_Count||':'||v_Chars(v_Count) );
    END LOOP;
END;

/*
--Nested Tables:
--Are like Index-By-Tables but:
--1) They can have non-sequential Key
--2) They don't have INDEX BY BINARY_INTEGER
*/

DECLARE
    TYPE t_Numbers IS TABLE OF NUMBER;
    v_Numbers   t_Numbers := t_Numbers(-1,4,7,8,9);
BEGIN
    DBMS_OUTPUT.PUT_LINE ( 'Example: 4' );
    v_Numbers.EXTEND(1); -- adds one empty elements
    v_Numbers(v_Numbers.COUNT) := 20; -- assigns 20 to the last element
    For v_Count IN v_Numbers.FIRST..v_Numbers.LAST LOOP
        DBMS_OUTPUT.PUT_LINE ( v_Count||':'||v_Numbers(v_Count) );
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('-------------');
END;

/*
--VARRAYS:
-- has a fixed upper bound on its size on Initialization
*/

DECLARE
    TYPE t_Numbers IS VARRAY(10) OF NUMBER;
    v_Numbers   t_Numbers := t_Numbers(-1,4,7,8,9,100,4,5,6);  -- 9 elemets of 10 possible
BEGIN
    DBMS_OUTPUT.PUT_LINE ( 'Example: 5' );
    --v_Numbers.EXTEND(1); -- adds one empty elements
    v_Numbers(v_Numbers.COUNT) := 20; -- assigns 20 to the last element
    v_Numbers(v_Numbers.COUNT) := 21; -- assigns 20 to the last element ,which overrides the last element
    For v_Count IN v_Numbers.FIRST..v_Numbers.LAST LOOP
        DBMS_OUTPUT.PUT_LINE ( v_Count||':'||v_Numbers(v_Count) );
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('-------------');
END;

/*
--Multilevel Collections:
--2 diemnsion collections
*/

declare
    TYPE t_Chars IS TABLE OF VARCHAR(100) INDEX BY BINARY_INTEGER;
    type t_Numbers  IS TABLE OF t_Chars  INDEX BY BINARY_INTEGER;
    v_Chars     t_Chars;
    v_MultiNumbers   t_Numbers;
    v_sep varchar(5);
BEGIN
    DBMS_OUTPUT.PUT_LINE ( 'Example: 6' );
    --print the Header
    DBMS_OUTPUT.PUT_LINE ( lpad('-',46,'-') );
    FOR x IN 1..12 LOOP
        For y IN 1..10 LOOP
            v_MultiNumbers (x)(y) :=x*y;
        END LOOP;
    END LOOP;
    DBMS_OUTPUT.PUT (lpad(0,3,'0')||' : ');
    v_sep:=' ';
    For y IN 1..10 LOOP
        DBMS_OUTPUT.PUT ( v_sep||lpad(y,3,'0'));
        v_sep:=',';
    END LOOP;
    DBMS_OUTPUT.PUT_LINE ( ' ' );
    --print the Values
    DBMS_OUTPUT.PUT_LINE ( lpad('-',46,'-') );
    FOR x IN 1..12 LOOP
        DBMS_OUTPUT.PUT (lpad(x,3,'0')||' : ');
        v_sep:=' ';
        For y IN 1..10 LOOP
            DBMS_OUTPUT.PUT ( v_sep||lpad(v_MultiNumbers(x)(y),3,' ') );
            v_sep:=',';
        END LOOP;
        DBMS_OUTPUT.PUT_LINE ( ' ' );
    END LOOP;
END;

The output of the above statements will look like this:

Example: 1
1:10
2:20
3:30
4:40
5:50
6:60
7:70
8:80
9:90
10:100
Example: 2
1:000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001
2:000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002
3:000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000003
4:000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000004
5:000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000005
6:000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000006
7:000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000007
8:000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000008
9:000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000009
10:00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000010
Example: 3
0:SWITZERLAND
1:GERMANY
2:NETHERLANDS
3:USA
4:BRAZIL

0:SWITZERLAND
1:GERMANY
2:NETHERLANDS
3:USA
4:BRAZIL
5:AUSTRIA
Example: 4
1:-1
2:4
3:7
4:8
5:9
6:20
-------------
Example: 5
1:-1
2:4
3:7
4:8
5:9
6:100
7:4
8:5
9:21
-------------
Example: 6
----------------------------------------------
000 :  001,002,003,004,005,006,007,008,009,010
----------------------------------------------
001 :    1,  2,  3,  4,  5,  6,  7,  8,  9, 10
002 :    2,  4,  6,  8, 10, 12, 14, 16, 18, 20
003 :    3,  6,  9, 12, 15, 18, 21, 24, 27, 30
004 :    4,  8, 12, 16, 20, 24, 28, 32, 36, 40
005 :    5, 10, 15, 20, 25, 30, 35, 40, 45, 50
006 :    6, 12, 18, 24, 30, 36, 42, 48, 54, 60
007 :    7, 14, 21, 28, 35, 42, 49, 56, 63, 70
008 :    8, 16, 24, 32, 40, 48, 56, 64, 72, 80
009 :    9, 18, 27, 36, 45, 54, 63, 72, 81, 90
010 :   10, 20, 30, 40, 50, 60, 70, 80, 90,100
011 :   11, 22, 33, 44, 55, 66, 77, 88, 99,110
012 :   12, 24, 36, 48, 60, 72, 84, 96,108,120