Create Schema-Level Type
CREATE OR REPLACE TYPE list_of_names_t IS
VARRAY(10) OF VARCHAR2 (100);
Let Others Use Your Type
GRANT EXECUTE ON list_of_names_t TO PUBLIC
All The Usual Methods
DECLARE
happyfamily list_of_names_t := list_of_names_t ();
BEGIN
happyfamily.EXTEND (4);
happyfamily (1) := 'Eli';
happyfamily (2) := 'Steven';
happyfamily (3) := 'Chris';
happyfamily (4) := 'Veva';
FOR l_row IN 1 .. happyfamily.COUNT
LOOP
DBMS_OUTPUT.put_line (happyfamily (l_row));
END LOOP;
END;
Limitations on DELETE with Varray
DECLARE
happyfamily list_of_names_t := list_of_names_t ();
BEGIN
happyfamily.EXTEND (4);
happyfamily (1) := 'Eli';
happyfamily (2) := 'Steven';
happyfamily (3) := 'Chris';
happyfamily (4) := 'Veva';
happyfamily.delete (2);
END;
Use TABLE Operator with Varray
DECLARE
happyfamily list_of_names_t := list_of_names_t ();
BEGIN
happyfamily.EXTEND (4);
happyfamily (1) := 'Eli';
happyfamily (2) := 'Steven';
happyfamily (3) := 'Chris';
happyfamily (4) := 'Veva';
/* Use TABLE operator to apply SQL operations to
a PL/SQL nested table */
FOR rec IN ( SELECT COLUMN_VALUE family_name
FROM TABLE (happyfamily)
ORDER BY family_name)
LOOP
DBMS_OUTPUT.put_line (rec.family_name);
END LOOP;
END;
Varray as Column Type in Database
CREATE OR REPLACE TYPE parent_names_t IS VARRAY (2) OF VARCHAR2 (100);
Silly Varray!
CREATE OR REPLACE TYPE child_names_t IS VARRAY (1) OF VARCHAR2 (100);
CREATE TABLE family
(
surname VARCHAR2 (1000)
, parent_names parent_names_t
, children_names child_names_t
)
Insert Varrays into Table
DECLARE
parents parent_names_t := parent_names_t ();
children child_names_t := child_names_t ();
BEGIN
DBMS_OUTPUT.put_line (parents.LIMIT);
parents.EXTEND (2);
parents (1) := 'Samuel';
parents (2) := 'Charina';
--
children.EXTEND;
children (1) := 'Feather';
--
INSERT INTO family (surname, parent_names, children_names)
VALUES ('Assurty', parents, children);
COMMIT;
END;
SELECT * FROM family
SURNAME | PARENT_NAMES | CHILDREN_NAMES | Assurty | [unsupported data type] | [unsupported data type] |
---|
Modify Limit on Existing Varray
CREATE OR REPLACE TYPE names_vat AS VARRAY (10) OF VARCHAR2 (80);
DECLARE
l_list names_vat := names_vat ();
BEGIN
DBMS_OUTPUT.put_line ('Limit of names_vat = ' || l_list.LIMIT);
END;
Modify Limit on Existing Varray
ALTER TYPE names_vat MODIFY LIMIT 100 INVALIDATE
DECLARE
l_list names_vat := names_vat ();
BEGIN
DBMS_OUTPUT.put_line ('Limit of names_vat = ' || l_list.LIMIT);
END;
Modify Limit on Existing Varray with Dynamic SQL
BEGIN
EXECUTE IMMEDIATE 'ALTER TYPE names_vat MODIFY LIMIT 200 INVALIDATE';
END;
DECLARE
l_list names_vat := names_vat ();
BEGIN
DBMS_OUTPUT.put_line ('Limit of names_vat = ' || l_list.LIMIT);
END;