So what is an Oracle Nested Table?

December 4, 2008

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








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers