Headlines for this series of articles, will remain as "Part II", for a little while. What we are going to talk about are Oracle database structures; Oracle nested tables, Oracle varray's, Oracle associative arrays, and to make things simple, and this, a returning page for you - and me, I thought that would be a good idea, to split it up.
Oracle first introduced nested tables and varying arrays in Oracle8i, as part of the object-relational model. However as early as version 7, PLSQL tables and Index-by where introduced. I think most of us where quite comfortable with index-by tables - and saw no real occasion to switch to these new types. Switching would properly mean de-normalize data - basically to much work. I suddenly saw no reason to explicitly initialize an index-by table or EXTEND before adding a row using a VARRAY.
-
Version 7 PLSQL tables
-
Version 8 Index-by tables
-
Version 9 Associative Arrays
Each version came with a set of new features, however Oracle 10G, must be the turning point where we all, have to rethink our preferences, due to a lot of new fascinating collection features. Many of these features will save you hours, coding and testing - however working with collections does use up memory in the program global area, PGA, of each session running the code.
If you compare the contents of two large tables, you could be consuming a very large amount of memory (in addition to that already consumed by the system global area, SGA).
So even though collections will save you time, you have to consider the effects in your environment, monitor it and, make sure that you are able to deal with it.
Below you will find information on; data dictionary objects, howto's using DML and nested tables. Basically this page is giving you in a short form, the practical approach on using nested tables.
cellspacing="0" cellpadding="0" width="100%" bgcolor="#d8d8c4" summary="" border="1">
> bgcolor="#cecece" colspan="2"> "A nested table is a table inside another table object in the database."> valign="top" width="20%" bgcolor="#dddddd"> Data Dictionary Objects width="80%" bgcolor="#dddddd"> colletion$
tab$
type$
cellspacing="1" cellpadding="0" width="100%" summary="" border="0"> > align="middle" width="33%"> dba_nested_tables align="middle" width="33%"> all_nested_tables align="middle" width="34%"> user_nested_tables > align="middle" width="33%"> dba_nested_table_cols align="middle" width="33%"> all_nested_table_cols align="middle" width="34%"> user_nested_table_cols > align="middle" width="33%"> dba_source align="middle" width="33%"> all_source align="middle" width="34%"> user_source > align="middle" width="33%"> dba_tables align="middle" width="33%"> all_tables align="middle" width="34%"> user_tables > align="middle" width="33%"> dba_tab_columns align="middle" width="33%"> all_tab_columns align="middle" width="34%"> user_tab_columns > align="middle" width="33%"> dba_types align="middle" width="33%"> all_types align="middle" width="34%"> user_types> valign="top" width="20%" bgcolor="#dddddd"> System Priviledges width="80%" bgcolor="#dddddd"> CREATE TYPE
CREATE ANY TYPE
DROP ANY TYPE
CREATE TABLE
CREATE ANY TABLE
DROP ANY TABLE > bgcolor="#cecece" colspan="2"> > valign="top" width="20%" bgcolor="#dddddd"> Nested Table Example width="80%" bgcolor="#dddddd"> CREATE TYPE genres_tab IS TABLE OF record_genre.genre_name%TYPE;
/
CREATE TABLE record_library (
library_id NUMBER,
name VARCHAR2(30),
record_genres_tab genres_tab)
NESTED TABLE record_genres_tab STORE AS genres_table;
/
SELECT table_name, nested
FROM user_tables;
set linesize 121
col table_name format a20
col data_type format a30
col table_type_name format a15
col parent_table_column format a10
SELECT column_name, data_type, data_length
FROM user_tab_columns
WHERE table_name = 'RECORD_LIBRARY';
SELECT table_name, table_type_owner, table_type_name,
parent_table_column
FROM user_nested_tables; > valign="top" width="20%" bgcolor="#dddddd"> Insert into a Nested Table width="80%" bgcolor="#dddddd">
INSERT INTO record_library (library_id, name, book_genres_tab)
VALUES (record_library_seq.NEXTVAL,'Brand New Record Library',
genres_tab('ROCK','BLUES', 'JAZZ', 'HIP HOP'));
/
SELECT * FROM record_library;
> valign="top" width="20%" bgcolor="#dddddd"> Update a Nested Table width="80%" bgcolor="#dddddd"> DECLARE
updated_genres_tab genres_tab;
BEGIN
updated_genres_tab :=
genres_tab('ROCK AND ROLL','BLUES', 'JAZZ', 'HIP HOP',
'METAL','CLASSIC','OPERA');
UPDATE record_library
SET record_genres_tab = updated_genres_tab;
END;
/
SELECT * FROM record_library; > valign="top" width="20%" bgcolor="#dddddd"> Drop a Nested Table width="80%" bgcolor="#dddddd">
SELECT table_name
FROM user_tables;
DROP TABLE record_library;
"You can't just drop a nested table. Insteed you have to use ALTER TABLE .... DROP COLUMN."
desc record_library
ALTER TABLE record_library
DROP COLUMN record_genres_tab;
> bgcolor="#cecece" colspan="2"> "Howto unnest a nested table"> valign="top" width="20%" bgcolor="#dddddd"> Collection Unnesting width="80%" bgcolor="#dddddd">
"The TABLE function enables you to work with collection elements"
1.
SELECT column_value FROM TABLE(SELECT record_genres_tab
FROM record_library
WHERE name = 'Brand New Record Library')
WHERE column_value LIKE '%ROCK%';
/
COLUMN_VALUE
------------------------------
ROCK AND ROLL
2.
UPDATE TABLE(SELECT record_genres_tab
FROM record_library
WHERE name = 'Brand New Record Library')
SET column_value = 'ROCK N ROLL'
WHERE column_value = 'ROCK AND ROLL';
3.
SELECT column_value FROM TABLE(SELECT record_genres_tab
FROM record_library
WHERE name = 'Brand New Record Library');
COLUMN_VALUE
------------------------------
ROCK N ROLL
BLUES
JAZZ
HIP HOP
METAL
CLASSIC
OPERAStatement one, only fetches elements from our nested table book_genes_tab inside the book_library database table.
The second statement, updates an element in the collection. This is only possible using nested tables.
The third and last statement shows an important this; We use the TABLE funtion to select our elements from the collection. However in earlier versions operating on VARRAY's - you would return a comma separated list of elements. Using TABLE we are now able to "UNEST" data from a nested table and show data top-down, like selecting from a "normal" table.