truncate table output
/
-------------------------------------------------------------------
-- Example of a VARRAY
-------------------------------------------------------------------
DECLARE  
    TYPE  array_type is VARRAY(10) of varchar2(25); --declare VARRAY type
    names array_type;                   --declare a variable of that type
BEGIN
    names := array_type('Sam','Joe','John',null);   --create an array with 4 slots 

    names(4) := 'Jack';                 --allowed since it was null before
    names.EXTEND(5);                    --need to extend beyond original 4 slots, 
                                        --but can only extend to max of 10 slots.     
    names(5) := 'Jill';
    names(6) := 'Jill2';
    names(8) := 'Jill3';

    insert into output values('&ltb>...USING VARRAY...&lt/b>'); 

    for i in 1..names.COUNT loop        --using COUNT as the upper bound
        insert into output 
            values('Element ' || i || ' is ' || names(i));
    end loop;
END;   
/
    

-----------------------------------------------------------------
-- Example of a TABLE (nested table)
-----------------------------------------------------------------
DECLARE  
    TYPE  array_type is TABLE of varchar2(25);      --declare TABLE type
    names array_type;                   --declare a variable of that type
    num   integer;
BEGIN
    names := array_type();              --create an empty table

    select count(*) into num from student;

    names.extend(num);                  --dynamic size allocation

    names(4) := 'Jack';             
    names(5) := 'Jill';
    names(6) := 'Jill2';
    names(7) := 'Jill3';

    insert into output values('&ltb>...USING TABLE...&lt/b>'); 

    for i in names.FIRST..names.LAST loop       --using FIRST..LAST as the range
        if (names(i) is not null) then          --only if element exists
            insert into output 
                values('Element ' || i || ' is ' || names(i));
        end if;
    end loop;
END;   
/


------------------------------------------------------------------
-- Example of a TABLE INDEX BY (associative array)
------------------------------------------------------------------
DECLARE  
    TYPE  array_type is TABLE of varchar2(25) index by varchar2(10);    --create TABLE INDEX BY type
    names array_type;                               --create associative array 
    idx   varchar2(10);
BEGIN
    names('first') := 'Sam';                        --notice varchar2 index             
    names('last')  := 'Sultan';
    names('sex')   := 'Male';
    names('addr')  := '123 main street';

    idx := names.FIRST;                             --get first index

    insert into output values('&ltb>...USING TABLE INDEX...&lt/b>'); 

    while (idx is not null) loop
        insert into output 
            values('Element ' || idx || ' is ' || names(idx));
        idx := names.NEXT(idx);                     --get next index
    end loop;
END;   
/


----------------------------------------------------------------
-- Example of loading an array from a SELECT query
----------------------------------------------------------------
DECLARE 
    TYPE name_type IS TABLE OF varchar2(100);           --declare the table type
    TYPE ssn_type  IS TABLE OF varchar2(11);            --declare the table type

    t_fname  name_type;                     --use the name type
    t_lname  name_type;                     --use the same name type
    t_ssn    ssn_type;                      --use the ssn type
BEGIN
    SELECT fname, lname, ssn  BULK COLLECT INTO t_fname, t_lname, t_ssn
          from student;

    insert into output values('&ltb>...LOADING USING SELECT...&lt/b>'); 

    for i in t_fname.first .. t_fname.last loop 
        insert into output 
            values (t_fname(i) ||' '|| t_lname(i) ||' '|| t_ssn(i));
    end loop;
END;
/


select * from output