Mega Code Archive
Modifying Nested Table Elements
<![CDATA[
Elements in a nested table can be modified individually: you can insert, update, and delete elements in a nested table.
You do this using the TABLE clause in conjunction with a subquery that selects the nested table.
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 );
7 /
SQL>
SQL> CREATE Or Replace TYPE nested_table_AddressType AS TABLE OF AddressType;
2 /
Type created.
SQL>
SQL> CREATE TABLE employee (
2 id INTEGER PRIMARY KEY,
3 first_name VARCHAR2(10),
4 last_name VARCHAR2(10),
5 addresses nested_table_AddressType
6 )
7 NESTED TABLE
8 addresses
9 STORE AS
10 nested_addresses;
Table created.
SQL>
SQL> INSERT INTO employee VALUES (
2 1, 'Steve', 'Brown',
3 nested_table_AddressType(
4 AddressType('2 Ave', 'City', 'MA', '12345'),
5 AddressType('4 Ave', 'City', 'CA', '54321')
6 )
7 );
1 row created.
SQL>
SQL> INSERT INTO TABLE (
2 SELECT addresses FROM employee WHERE id = 1
3 ) VALUES (
4 AddressType('5 Ave', 'Uptown', 'NY', '55512')
5 );
1 row created.
SQL>
SQL> select * from employee;
ID FIRST_NAME LAST_NAME ADDRESSES(STREET, CITY, STATE, ZIP)
----------------------------------------------------------------------------------------------------------------
1 Steve Brown NESTED_TABLE_ADDRESSTYPE(ADDRESSTYPE('2 Ave', 'City', 'MA', '12345'), ADDRESSTYPE('4 Ave', 'City', 'CA', '54321'), ADDRESSTYPE('5 Ave', 'Uptown', 'NY', '55512'))
SQL>
SQL> drop table employee;
Table dropped.
SQL>
]]>