Live ORACLE

DROP TABLE depts CASCADE CONSTRAINTS PURGE
/

DROP TYPE dnames_var
/

-- By using a varray, we put an upper limit on the number of elements
-- and ensure they always come back in the same order
CREATE TYPE dnames_var IS VARRAY(7) OF VARCHAR2(30);
/

CREATE TABLE depts (
region     VARCHAR2(25),
dept_names DNAMES_VAR);

BEGIN
INSERT INTO depts
VALUES     ('Europe',
           Dnames_var('Shipping','Sales','Finance'));
INSERT INTO depts
VALUES     ('Americas',
           Dnames_var('Sales','Finance','Shipping'));
INSERT INTO depts
VALUES     ('Asia',
           Dnames_var('Finance','Payroll','Shipping','Sales'));
COMMIT;
END;
/

DECLARE
new_dnames   DNAMES_VAR := Dnames_var('Benefits','Advertising','Contracting','Executive',
                                     'Marketing');
some_dnames  DNAMES_VAR;
BEGIN
UPDATE depts
SET    dept_names = new_dnames
WHERE  region = 'Europe';
COMMIT;
SELECT dept_names
INTO   some_dnames
FROM   depts
WHERE  region = 'Europe';
FOR i IN some_dnames.FIRST.. some_dnames.LAST LOOP
 dbms_output.Put_line('dept_names = '
                      ||Some_dnames(i));
END LOOP;
END;
/  

Using INSERT, UPDATE, DELETE, and SELECT Statements With Varrays