Nested Tables as Collection Data Type and a Table Column
Sometimes, a table is designed with one column that can hold an array. An array in database design is a table. So, in Oracle, you can create a type as a table and then use this type as a column in another table. What we end up with is a nested table as a table column or – put another way – a table type as a table column.
Nested Tables
As a type, a nested table is an unbounded, subscripted object type that stores a collection of data. That means it can have as many elements as you add to it and the elements will be numbered. Nested tables start dense so that every element is numbered as it is created. After a while, from inserting, deleting and inserting some more, the subscripts assigned can become sparse, or non-consecutive.
Nested tables and other collections in Oracle have their own set of Methods for traversing and gaining information on the collection. Elements include FIRST, LAST, NEXT(i), PREVIOUS(i), etc.
Used as a Collection Data Type
Here’s an example of the Nested Table Collection Data Type in action. It is declared here as a database object. It can also be declared within the PL/SQL block in the declare section by dropping the CREATE OR REPLACE portion.
CREATE OR REPLACE TYPE StateList IS TABLE OF varchar2(2);
declare
sl StateList := StateList();
i integer;
begin
sl := StateList('FL','GA','AL');
for i in sl.FIRST..sl.LAST loop
dbms_output.put_line (i || ': ' || sl(i));
end loop;
sl.delete(2);
i := sl.FIRST;
while i is not null loop
dbms_output.put_line (i || ': ' || sl(i));
i:=sl.NEXT(i);
end loop;
end;
The sl variable (declared as a StateList datatype) is first populated with ‘FL’,’GA’,’AL’. Then it prints out all of the elements, one after another using a FOR LOOP. They were just inserted so the subscripts will be a consistent numbering and the FOR NEXT loop will spit out numbers, one after the other, from the first number given by sl.FIRST and the last number given by sl.LAST.
1: FL 2: GA 3: AL
After, the second element is deleted using sl.delete(2). ‘GA’ is off the list. This time, we can’t just run them out one after another because the second element is missing. The while loop used initializes i with the first index using sl.first. Then, using a while loop, the elements are printed out. The next value of i is dictated by sl.NEXT(i), which gives the subscript of the next element in the collection.
1: FL 3: AL
Used as a Table Column
When you use a nested table within another table, it is actually stored in a separate storage table. The storage table holds a value that traces back to the parent table row. The STORE AS clause that comes after the CREATE TABLE statement names the separate storage table and uses that name in creating the index.
The statement below creates a table called tbl_theme_parks and another storage table called locations_tab.
CREATE OR REPLACE TYPE StateList IS TABLE OF varchar2(2);
CREATE TABLE tbl_theme_parks (
park_id integer,
park_name varchar2(20),
locations StateList)
NESTED TABLE locations STORE AS locations_tab;
insert into tbl_theme_parks values (1,'Busch Gardens', StateList('FL','VA'));
insert into tbl_theme_parks values (2,'Sea World', StateList('FL','TX','CA'));
insert into tbl_theme_parks values (3,'Adventure Island', StateList('FL'));
insert into tbl_theme_parks values (4,'Wally World', null);
insert into tbl_theme_parks values (5,'Six Gun Territory', null);
Notice how the locations field is populated using the StateList data type. A couple of the records have a null value in the locations column. If we select from the table, we will see the locations in the table.
PARK_ID PARK_NAME LOCATIONS(ELEMENT) ---------- -------------------- --------------------------------------------------- 1 Busch Gardens STATELIST(FL,VA) 2 Sea World STATELIST(FL,TX,CA) 3 Adventure Island STATELIST(FL) 4 Wally World STATELIST() 5 Six Gun Territory STATELIST()
When querying a nested table, it doesn’t work to use an equation in the WHERE clause.
select * from tbl_theme_parks where locations = 'FL' * Error at line 1 ORA-00932: inconsistent datatypes: expected SYS.STATELIST got CHAR
Instead, reference the nested table using the TABLE function. Notice there is no joining between tbl_theme_parks and table(locations). This is where the external storage’s reference back to the parent row comes into play.
select * from tbl_theme_parks, table(locations) loc where loc.column_value = 'FL'; PARK_ID PARK_NAME LOCATIONS(ELEMENT) COLUMN_VALUE ---------- -------------------- ------------------- ------------ 1 Busch Gardens STATELIST(FL,VA) FL 2 Sea World STATELIST(FL,TX,CA) FL 3 Adventure Island STATELIST(FL) FL 3 rows selected.
Because of the way the TABLE function works with the table in an implicit join, when you run a catch-all query, the rows with null locations will not be shown. Notice there is no mention of Wally World or Six Gun Territory.
select park_name, loc.column_value from tbl_theme_parks, table(locations) loc; PARK_NAME COLUMN_VALUE -------------------- ------------ Busch Gardens FL Busch Gardens VA Sea World FL Sea World TX Sea World CA Adventure Island FL
To join the tables and return all of the rows, even where the column_value is null, we need to write an outer join. For that, we can resort to the outer join operator (+).
set null *null* select park_name, loc.column_value from tbl_theme_parks, table(locations) (+) loc; PARK_NAME COLUMN_VALUE -------------------- ------------ Busch Gardens FL Busch Gardens VA Sea World FL Sea World TX Sea World CA Adventure Island FL Wally World *null* Six Gun Territory *null*
By the way, querying the nested table’s storage table is a no-no. Not gonna work. Nice thinkin, though.
select * from locations_tab * Error at line 1 ORA-22812: cannot reference nested table column's storage table