So what is an Oracle Nested Table?

Let’s take some of the
confusion out of Oracle nested tables. This article takes a step by step
approach to create a table with a nested table and then query that table while
showing some key DBA views we can use to describe the structures.

I think we have all come to grips with what a database table
is these days. However, when we start extending the very nature of a simple
table we tend to get into trouble. I liken this to taking simple SQL statements
and extending into correlated subqueries where some of us just start to get
lost and unable to relate. Nested tables are much like this, they take us to a
form of abstraction we just don’t like thinking about. After all, when talking
about something like nested tables, we are sort of going out of bounds and
creating a relationship within a table that could easily be done with two
distinct and separate tables.

In the purest sense, a nested table is nothing more than a
normal table that has a column whose datatype is another table. Call it a table
within a table, a column that is of type table, or a column that has for its
values another table. Confused yet? Let’s step through the process.

First, we must create our own object type called ADDRESS_TYPE.
Then we create a table of ADDRESS_TYPE called ADDRESS_TABLE_TYPE. The table
ADDRESS_TABLE_TYPE is what we will use as our nested table column type. All of
this sounds quite confusing. Just try to remember that every attribute of a
table needs some form of data type. We are creating our own data type here
called ADDRESS_TABLE_TYPE that is a table of the type address_type. Below are
the two DDL statements with a DESCribe of each of them to show you that
ADDRESS_TYPE and ADDRESS_TABLE_TYPE really have the same attributes. It’s just
that ADDRESS_TABLE_TYPE is a TABLE of ADDRESS_TYPE—denoting that we will be
able to store multiple addresses for every row in the table we will be
defining.


CREATE TYPE address_type AS OBJECT
(ADDR_LINE VARCHAR2(50),
CITY VARCHAR2(50),
STATE VARCHAR2(02),
ZIP VARCHAR2(05));
/
CREATE TYPE address_table_type AS TABLE OF address_type;
/


SQL> DESC address_type
Name Type
—————— ————–
ADDR_LINE VARCHAR2(50)
CITY VARCHAR2(50)
STATE VARCHAR2(2)
ZIP VARCHAR2(5)

SQL> DESC address_table_type
address_table_type TABLE OF ADDRESS_TYPE
Name Type
—————— ————–
ADDR_LINE VARCHAR2(50)
CITY VARCHAR2(50)
STATE VARCHAR2(2)
ZIP VARCHAR2(5)

Since many of us like to use the DBA internal views to look
at our objects, here is the SQL to look at our two newly created types. Nothing
too exciting here but just note that they are of OBJECT_TYPE equal to
TYPE—denoting that they are user-defined types. Also, note that in the
DBA_TYPES view while the ADDRESS_TYPE is an OBJECT the ADDRESS_TABLE_TYPE is a
collection.


SQL> select object_name,object_type
from dba_objects
where object_name like ‘ADDRESS_%’;
OBJECT_NAME OBJECT_TYPE
——————– ————–
ADDRESS_TYPE TYPE
ADDRESS_TABLE_TYPE TYPE
SQL> select TYPE_NAME,TYPECODE,ATTRIBUTES
from dba_types
where type_name like ‘ADDRESS_%’;
TYPE_NAME TYPECODE ATTRIBUTES
——————- —————- ———-
ADDRESS_TYPE OBJECT 4
ADDRESS_TABLE_TYPE COLLECTION 0

You can also extract what the DDL sort of looked like from
the DBA_SOURCE view if you ever want to investigate what someone else might
have created. Sometimes this helps you get your head around some interesting
naming conventions that will throw the best of us off.


SQL> select text
from dba_source
where name = ‘ADDRESS_TYPE’;
TEXT
——————————————-
TYPE address_type AS OBJECT
(ADDR_LINE VARCHAR2(50),
CITY VARCHAR2(50),
STATE VARCHAR2(02),
ZIP VARCHAR2(05));
7 rows selected.
SQL> select text
from dba_source
where name = ‘ADDRESS_TABLE_TYPE’;
TEXT
——————————————————————-
TYPE address_table_type AS TABLE OF address_type;

So now, in order to create a table,
we just need to specify a column name (ADDRESSES in this case) and our newly
created TYPE (ADDRESS_TABLE_TYPE). Quite simple. Below is the DDL to create the
table along with some SQL on how we might want to look at this newly created
table. When we look at the DBA_TAB_COLS view, we can see the data type
ADDRESS_TABLE_TYPE and then, additionally, we can look at the nested DBA view
DBA_NESTED_TABLES and DBA_NESTED_TABLE_COLS.


CREATE TABLE employee (
Name VARCHAR2(20),
addresses ADDRESS_TABLE_TYPE)
NESTED TABLE addresses STORE AS addresses_table;
SQL> DESC employee
Name Type
——————– —————–
NAME VARCHAR2(20)
ADDRESSES ADDRESS_TABLE_TYPE
SQL> DESC addresses_table
Name Type
——————- ————-
ADDR_LINE VARCHAR2(50)
CITY VARCHAR2(50)
STATE VARCHAR2(2)
ZIP VARCHAR2(5)
SELECT table_name, column_name, data_type, data_length
FROM dba_tab_cols
WHERE table_name = ‘EMPLOYEE’;
TABLE_NAME COLUMN_NAME DATA_TYPE DATA_LENGTH
———- ——————- ——————- ———–
EMPLOYEE ADDRESSES ADDRESS_TABLE_TYPE 16
EMPLOYEE NAME VARCHAR2 20
EMPLOYEE SYS_NC0000200003$ RAW 16
SQL> SELECT table_name, table_type_name,parent_table_column
FROM dba_nested_tables
WHERE table_name = ‘ADDRESSES_TABLE’;
TABLE_NAME TABLE_TYPE_NAME PARENT_TABLE_COLUMN
—————- ————————– ———————–
ADDRESSES_TABLE ADDRESS_TABLE_TYPE ADDRESSES
SQL> select table_name,column_name,data_type
from dba_nested_table_cols
where table_name = ‘ADDRESSES_TABLE’;
TABLE_NAME COLUMN_NAME DATA_TYPE
——————– —————————— ————
ADDRESSES_TABLE NESTED_TABLE_ID RAW
ADDRESSES_TABLE STATE VARCHAR2
ADDRESSES_TABLE CITY VARCHAR2
ADDRESSES_TABLE SYS_NC_ROWINFO$ ADDRESS_TYPE
ADDRESSES_TABLE ADDR_LINE VARCHAR2
ADDRESSES_TABLE ZIP VARCHAR2

If we now want to actually use this
table and nested table we can issue some semi-simplistic DML commands. Below
you see that we first insert into the table while leaving the nested table empty
and then subsequently insert into the nested table using a key column NAME. To
generate a result set you can make use of the TABLE function.


SQL> INSERT INTO employee (name, addresses)
VALUES (‘Dude’, address_table_type());
1 row created.
INSERT INTO TABLE(SELECT addresses FROM employee WHERE name = ‘Dude’)
VALUES (‘addr1’, ‘city-1′, ’01’, ‘11111’);
INSERT INTO TABLE(SELECT addresses FROM employee WHERE name = ‘Dude’)
VALUES (‘addr2’, ‘city-2′, ’02’, ‘22222’);
INSERT INTO TABLE(SELECT addresses FROM employee WHERE name = ‘Dude’)
VALUES (‘addr3’, ‘city-3′, ’03’, ‘33333’);
SQL> select t1.name, t2.* from employee t1, table(t1.addresses) t2;
NAME ADDR_L CITY ST ZIP
—– —— ——- — —–
Dude addr1 city-1 01 11111
Dude addr2 city-2 02 22222
Dude addr3 city-3 03 33333

Nested tables, while a bit confusing, are quite a neat little feature within
Oracle. Now, while most of us might be utterly confused by how these nested
tables are used, I think that nested tables give us some interesting options
when defining objects, especially when we have to reuse common attributes
across tables. For a DBA, as you can see we need to make sure we are able to
query the DBA views that allow us to get a clear understanding of how our
databases are defined.

»


See All Articles by Columnist
James Koopmann

James Koopmann
James Koopmann
James Koopmann has fourteen years of database design, development and performance tuning experience. In addition, he has extensive database administration experience in Oracle and other relational databases in production environments, specializing in performance tuning of database engines and SQL based applications. Koopmann is an accomplished author with several technical papers in various Oracle related publications such as Oracle Magazine, Oracle Professional and SQL>UPDATE_RMOUG. He is a featured author and database expert for DatabaseJournal, a member of the editorial review committee for Select Journal (The Magazine for the International Oracle Users Group), an Oracle Certified Professional DBA and noted speaker at local Oracle User Groups around the country.

Latest Articles