What Is An Oracle Nested Table

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 -------------------NAME ADDRESSES

Type ----------------VARCHAR2(20) 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 ADDRESSES_TABLE ADDRESSES_TABLE ADDRESSES_TABLE ADDRESSES_TABLE ADDRESSES_TABLE

-----------------------------NESTED_TABLE_ID STATE CITY SYS_NC_ROWINFO$ ADDR_LINE ZIP

-----------RAW VARCHAR2 VARCHAR2 ADDRESS_TYPE VARCHAR2 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 ----Dude Dude Dude

ADDR_L -----addr1 addr2 addr3

CITY ------city-1 city-2 city-3

ST -01 02 03

ZIP ----11111 22222 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.