SQL> CREATE or replace TYPE phoneVArray AS VARRAY(3) OF VARCHAR2(14);
2 /
Type created.
SQL>
SQL> CREATE or replace TYPE addressType AS OBJECT (
2 street VARCHAR2(15),
3 city VARCHAR2(15),
4 state CHAR(2),
5 zip VARCHAR2(5),
6 phone_numbers phoneVArray
7 );
8 /
Type created.
SQL>
SQL> CREATE or replace TYPE addressTypeNestedTable AS TABLE OF addressType;
2 /
Type created.
SQL>
SQL>
SQL> -- create the tables
SQL>
SQL> CREATE TABLE emp (
2 id INTEGER PRIMARY KEY,
3 fname VARCHAR2(10),
4 lname VARCHAR2(10),
5 addresses addressTypeNestedTable
6 )
7 NESTED TABLE
8 addresses
9 STORE AS
10 nested_addresses;
Table created.
SQL>
SQL> INSERT INTO emp VALUES (
2 1, 'Steve', 'Brown',
3 addressTypeNestedTable(
4 addressType('2 Street', 'town', 'MA', '12345',
5 phoneVArray(
6 '(800)-555-1211',
7 '(800)-555-1213'
8 )
9 ),
10 addressType('4 Street', 'L Town', 'CA', '54321',
11 phoneVArray(
12 '(800)-555-1211',
13 '(800)-555-1212',
14 '(800)-555-1213',
15 '(800)-555-1214'
16 )
17 )
18 )
19 );
phoneVArray(
*
ERROR at line 11:
ORA-22909: exceeded maximum VARRAY limit
SQL>
SQL>
SQL> drop type addressTypeNestedTable force;
Type dropped.
SQL> drop type addressType force;
Type dropped.
SQL> drop type phoneVArray force;
Type dropped.
SQL> drop TABLE emp;
Table dropped.