varray syntax in oracle code example

Example: varray in oracle

-- can be stored in database
-- cannot delete data here once inserted

DECLARE
    TYPE customer_type IS VARRAY(4) OF VARCHAR(20);         -- you can store only 4(as VARRAY(4)) values here
    customer_table customer_type := customer_type();        -- initialization of collection
    v_indx NUMBER
BEGIN

    customer_table.EXTEND(4);               -- you have to do this for using the table

    customer_table(1) := 'MIKE';
    customer_table(2) := 'MIKE';
    customer_table(3) := 'MIKE';
    customer_table(4) := 'MIKE';
    customer_table(4) := 'MIKE';        -- Should be in sequence throws error

    v_indx := customer_table.FIRST;         -- returns first index (customer_table.LAST - returns last index)

    -- traversing using while loop

    WHILE v_indx IS NOT NULL 
    LOOP
        dbms_output.put_line(customer_table(v_indx));
        v_indx := customer_table.NEXT(v_indx);
    END LOOP display_loop;

END;
/

Tags:

Misc Example