Mega Code Archive
Manipulating the VARRAY with The VARRAY Self-join
<![CDATA[
A statement can be created that joins the values of the virtual table (created with the TABLE function) to the rest of the values in the table
SQL>
SQL> CREATE OR REPLACE TYPE mem_type IS VARRAY(10) of VARCHAR2(15)
2 /
Type created.
SQL>
SQL> CREATE TABLE club (Name VARCHAR2(10),
2 Address VARCHAR2(20),
3 City VARCHAR2(20),
4 Phone VARCHAR2(8),
5 Members mem_type)
6 /
Table created.
SQL>
SQL> INSERT INTO club VALUES ('AL','111 First St.','Mobile',
2 '222-2222', mem_type('Brenda','Richard'));
1 row created.
SQL>
SQL> INSERT INTO club VALUES ('FL','222 Second St.','Orlando',
2 '333-3333', mem_type('Gen','John','Steph','JJ'));
1 row created.
SQL>
SQL> SELECT c.name, c.address, p.column_value
2 FROM club c, TABLE(c.members) p;
NAME ADDRESS COLUMN_VALUE
---------- -------------------------------------------------- ---------------
AL 111 First St. Brenda
AL 111 First St. Richard
FL 222 Second St. Gen
FL 222 Second St. John
FL 222 Second St. Steph
FL 222 Second St. JJ
6 rows selected.
SQL>
SQL> drop table club;
Table dropped.
SQL> drop type mem_type;
Type dropped.
SQL>
]]>