Nested tables let you implement ID dependent tables. ID dependent tables are solely dependent on another table. Data modelers choose between ID- and non-ID dependent tables. The non-ID dependent table is the most common. It describes implementing ordinary tables. ID dependent tables require you to nest a table in another. Most designers opt for non-ID dependent relationships because they avoid nesting tables. I recently added a set of
DELETE statements for nested tables in this post and expanded that with how to update a table view column (avoiding an ORA-25015 error).
Some teams denormalize the table by creating multiple columns. A classic example is an address table. Since an address can have multiple street address values because of building, suite, and apartment numbers. You’ve probably seen them implemented as
STREET_ADDRESS2, et cetera. The problem with this design is that a customer may require third street address column. An alternative is to implement a comma delimited list (technically, violating first normal form).
The following implements an ID dependent model or nested table:
1. Create a scalar collection type, implement the collection in an address table, and seed it with two rows. You raise a
PLS-00329 exception if you attempt to assign a multiple column structure as a base datatype of a user-defined type (UDT). UDTs are by default collecitons of schema-level scalar datatypes. A scalar datatype contains only one thing, like a number or string.
CREATE TABLE contact_book ( contact_id NUMBER , first_name VARCHAR2(20) , middle_name VARCHAR2(20) , last_name VARCHAR2(20)); INSERT INTO contact_book VALUES ( 1,'Brad',NULL,'Scott'); INSERT INTO contact_book VALUES ( 2,'Jeff',NULL,'Kenney');
2. Create a scalar collection type, implement the collection in an address table, and seed it with two rows:
CREATE TYPE street_list IS TABLE OF VARCHAR2(30); / CREATE TABLE address_book ( address_book_id NUMBER , street_address STREET_LIST , city VARCHAR2(30) , state VARCHAR2(2) , postal_code VARCHAR2(10)) NESTED TABLE street_address STORE AS street_table; INSERT INTO address_book VALUES ( 1, street_list('203 North La Salle','Suite 1400'), 'Chicago', 'IL', '60601'); INSERT INTO address_book VALUES ( 2, street_list('203 North La Salle','Suite 1800'), 'Chicago', 'IL', '60601');
3. Create a function to convert the varying number of rows into a single string with line returns (that’s the
CREATE OR REPLACE FUNCTION format_address(set_in STREET_LIST) RETURN VARCHAR2 IS retval VARCHAR2(2000); CURRENT NUMBER; BEGIN IF set_in IS NOT NULL AND set_in IS NOT EMPTY THEN CURRENT := set_in.FIRST; WHILE CURRENT IS NOT NULL LOOP retval := retval || set_in(CURRENT)||CHR(10); CURRENT := set_in.next(CURRENT); END LOOP; END IF; RETURN retval; END format_address; /
4. Query the collection with the
FORMAT_ADDRESS function, which resolves the one to many join between the scalar columns and array returned by the
SELECT cb.first_name || DECODE(cb.middle_name,NULL,' ',cb.middle_name||' ') || cb.last_name||CHR(10) || format_address(ab.street_address) || ab.city||', ' || ab.state||' ' || ab.postal_code||CHR(10) mail_label FROM contact_book cb JOIN address_book ab ON cb.contact_book_id = ab.contact_book_id WHERE ab.street_address IS NOT NULL OR ab.street_address IS NOT EMPTY;
This provides you with a formatted address list for envelops:
Brad Scott 203 North La Salle Suite 1400 Chicago, IL 60601 Jeff Kenney 203 North La Salle Suite 1800 Chicago, IL 60601
How to test the state of nested columns?
Somebody posted a comment and asked how you could determine if a nested table was null. I thought showing how you can find null, empty, or populated collections would be more helpful. Here’s a quick example that uses the same scalar collection of strings found earlier in this post. You can create this sample case by using the preceding code, plus the following:
Insert another two rows. One with a
NULL value for the
STREET_ADDRESS column and another with an empty SQL collection in the same column, like this:
INSERT INTO address_book VALUES -- Inserts a NULL collection. ( 3, NULL, 'San Francisco','CA','94106'); INSERT INTO address_book VALUES -- Inserts an empty collection. ( 4, street_list(), 'Los Angeles','CA','90017');
You can query to determine which rows have a
NULL value, an empty SQL collection, or a populated SQL collection. The following query uses the CASE statement to return the values:
SELECT address_book_id , CASE WHEN street_address IS NULL THEN 'Null' WHEN street_address IS EMPTY THEN 'Empty' ELSE 'Populated' END AS content , city FROM address_book;
It returns the following data:
ADDRESS_BOOK_ID CONTENT CITY --------------- --------- -------------- 1 Populated Chicago 2 Populated Chicago 3 Null San Francisco 4 Empty Los Angeles
A quick note: If you try to select the contents of the populated contents by using the column name, it raises the following error because the native type is really a SQL collection. Unfortunately, my test schema is named PLSQL, but this is a SQL data type.
SQL> SELECT address_book_id 2 , CASE 3 WHEN street_address IS NULL THEN 'Null' 4 WHEN street_address IS EMPTY THEN 'Empty' 5 ELSE street_address 6 END AS content 7 , city 8 FROM address_book; ELSE street_address * ERROR at line 5: ORA-00932: inconsistent datatypes: expected CHAR got PLSQL.STREET_LIST
You can see the data by rewriting the query this way. The table that contains the nested table must precede the nested table column as an argument to the
TABLE function. As you can see it repeats the non-repeating data types because of the cross join (Cartesian product). That’s why a normalizing function was used earlier in the blog post.
The join does not work against rows containing either a null or empty collection because the
TABLE function can’t process them. Therefore, you needn’t worry about them in this scenario.
SQL> SELECT address_book_id 2 , CASE 3 WHEN street_address IS NULL THEN 'Null' 4 WHEN street_address IS EMPTY THEN 'Empty' 5 ELSE nested_table.column_value 6 END AS content 7 , city 8 FROM address_book CROSS JOIN TABLE(street_address) nested_table; ADDRESS_BOOK_ID CONTENT CITY --------------- ------------------------------ ------------------------------ 1 203 North La Salle Chicago 1 Suite 1400 Chicago 2 203 North La Salle Chicago 2 Suite 1800 Chicago
Updates into these types and how to avoid an
ORA-25015 error and in notes. Hope this helps a few folks.