JoeGarrepy.com Oracle Nested Tables Example

Programming/
Tools
      Oracle
      JavaScript
      C#

Fun Stuff
      Music
      Martial Arts
      Robotech

Other
      Main

DISCLAIMER

This disclaimer is intended to cover all software/freeware, and other various files which are downloadable through or from this site.
Joe Garrepy, isp, and web host can not be held responsible for any software downloaded from this site. All freeware is downloaded at your own risk. Software which is listed on this site is assumed to work properly, but may or may not have different effects on different computer systems. The same rules apply to these programs. Joe Garrepy assumes no responsibility for those unusual occurrences where software has adverse effects on users computer. Joe Garrepy does not warrant these programs/files, and as such, can offer no guidelines in which to use them. All freeware/software/files, trademarks, service marks, and copyrights are acknowledged as belonging to their owners. Any of these which the owner wishes to be removed will be done so immediately. By downloading any software or viewing pages in which software can be downloaded, you agree to be bound to this agreement.

JoeGarrepy.com Oracle Nested Tables Example

Another new feature of Oracle is the ability to build Nested Tables or tables that have columns that are tables. This is neat because you can build a relational data structure inside of one table. The example below builds a table called ENTITY. The data stored in this table traditionally would have to be stored in 4 different tables (base entity table, a repeating name table, a repeating person data table and a repeating employment data table). Instead only the base entity table exists with its children as columns nested inside of it. The script below not only creates a table with multiple nested tables as columns but shows how to select from it and perform DML on the table and precision DML on the nested tables.

SQL Script

/*
  -- ****************************************************************** --
   9/30/2003 NESTED TABLES Create Script (Joe Garrepy) VERSION 1.0
  -- ****************************************************************** --
   The following script creates several TYPE objects and one TABLE object
   to illustrate the creation of NESTED tables.  The script also shows
   how to insert rows into the Regular table and also work with percision
   on the nested tables (all DML statements).  It also contains SQL code
   to bring back values from the table.
  -- ****************************************************************** --
*/
--> Create Name Column with "ROW" Type and "TABLE" Type
CREATE OR REPLACE TYPE "ROW_NAME"
  AS OBJECT
  (FIRST VARCHAR2(60),
   LAST VARCHAR2(60),
   MIDDLE VARCHAR2(30),
   PREFIX VARCHAR2(10),
   SUFFIX VARCHAR2(10),
   CREATE_DATE DATE,
   CHANGE_DATE DATE,
   CREATE_USER VARCHAR2(30),
   CHANGE_USER VARCHAR2(30))
/
CREATE OR REPLACE TYPE "TAB_NAME"
  AS TABLE OF ROW_NAME
/
--> Create Person Column with "ROW" Type and "TABLE" Type
CREATE OR REPLACE TYPE "ROW_PERSON"
  AS OBJECT
  (SSN VARCHAR2(9),
   GENDER VARCHAR2(1),
   BDATE DATE,
   ETHNICITY VARCHAR2(2),
   MARITAL_STATUS VARCHAR2(2),
   HAIR VARCHAR2(10),
   EYES VARCHAR2(10),
   HEIGHT VARCHAR2(10),
   CREATE_DATE DATE,
   CHANGE_DATE DATE,
   CREATE_USER VARCHAR2(30),
   CHANGE_USER VARCHAR2(30))
/
CREATE OR REPLACE TYPE "TAB_PERSON"
  AS TABLE OF ROW_PERSON
/
--> Create Employment Column with "ROW" Type and "TABLE" Type
CREATE OR REPLACE TYPE "ROW_EMPLOYMENT"
  AS OBJECT
  (JOB_TITLE VARCHAR2(60),
   START_DATE DATE,
   TERMINATED VARCHAR2(1),
   END_DATE DATE,
   SALARY NUMBER(8,4),
   CREATE_DATE DATE,
   CHANGE_DATE DATE,
   CREATE_USER VARCHAR2(30),
   CHANGE_USER VARCHAR2(30)
   )
/
CREATE OR REPLACE TYPE "TAB_EMPLOYMENT"
  AS TABLE OF ROW_EMPLOYMENT
/
--> Create table with UID and TAB_NAME & TAB_PERSON tables as columns
CREATE TABLE "ENTITY"
   (    "UIDN"          NUMBER(10) NOT NULL,
        "NAME"          "TAB_NAME" DEFAULT "TAB_NAME"(),
        "PERSON"        "TAB_PERSON" DEFAULT "TAB_PERSON"(),
        "EMPLOYMENT"    "TAB_EMPLOYMENT" DEFAULT "TAB_EMPLOYMENT"(),
        "DEAD"          VARCHAR2(1) DEFAULT 'N',
        "DEAD_DATE"     DATE,
        "CREATE_DATE"   DATE DEFAULT SYSDATE NOT NULL ENABLE,
        "CHANGE_DATE"   DATE,
        "CREATE_USER"   VARCHAR2(30) DEFAULT USER,
        "CHANGE_USER"   VARCHAR2(30),
         PRIMARY KEY ("UIDN"))
 NESTED TABLE "NAME" STORE AS "ENTITY_NAME"
 NESTED TABLE "PERSON" STORE AS "ENTITY_PERSON"
 NESTED TABLE "EMPLOYMENT" STORE AS "ENTITY_EMPLOYMENT"
/
COMMENT ON TABLE "ENTITY" IS 'Entity Base Table with repeating data as Nested Tables';
COMMENT ON COLUMN "ENTITY"."UIDN" IS 'Unique Identifaction Number/KEY';
COMMENT ON COLUMN "ENTITY"."NAME" IS 'Nested Table Storing Name Data for an entity';
COMMENT ON COLUMN "ENTITY"."PERSON" IS 'Nested Table Storing Person Data for an entity';
COMMENT ON COLUMN "ENTITY"."EMPLOYMENT" IS 'Nested Table Storing Employment related data for an entity';
COMMENT ON COLUMN "ENTITY"."DEAD" IS 'Y if entity is dead else N';
COMMENT ON COLUMN "ENTITY"."DEAD_DATE" IS 'Date of Death';
--> Now lets insert some records
INSERT INTO entity VALUES
(1,TAB_NAME(ROW_NAME('Orion','Pax',NULL,NULL,NULL,SYSDATE,NULL,USER,NULL),
            ROW_NAME('Optimus','Prime',NULL,NULL,NULL,SYSDATE,NULL,USER,NULL)),
 TAB_PERSON(ROW_PERSON('000000000','M','01-JAN-1985','AB',NULL,NULL,NULL,'10 meters',SYSDATE,NULL,USER,NULL)),
 TAB_EMPLOYMENT(ROW_EMPLOYMENT('Autobot Leader','01-JAN-1985',NULL,NULL,1000,SYSDATE,NULL,USER,NULL)),
 'N',NULL,SYSDATE,NULL,USER,NULL);
INSERT INTO entity VALUES
(2,TAB_NAME(ROW_NAME('Megatron',NULL,NULL,NULL,NULL,SYSDATE,NULL,USER,NULL)),
 TAB_PERSON(ROW_PERSON('000000001','M','01-JAN-1985','DC',NULL,NULL,NULL,'10 meters',SYSDATE,NULL,USER,NULL)),
 TAB_EMPLOYMENT(ROW_EMPLOYMENT('Decepticon Leader','01-JAN-1985',NULL,NULL,995,SYSDATE,NULL,USER,NULL)),
 'N',NULL,SYSDATE,NULL,USER,NULL);
COMMIT;
--> View what whe have inserted by flatening out the column collections
SELECT src.uidn,
       n.*,
       p.*,
       e.*,
       dead,
       dead_date,
       src.create_date,
       src.change_date,
       src.create_user,
       src.change_user
FROM ENTITY src,
     TABLE(src.name) n,
     TABLE(src.person) p,
     TABLE(src.employment) e;
--> Insert specific records into column of collections
/* Megatron's name changed during Transformers the Movie so lets insert
   a new record into the collection for Names.
   The first thing that needs to be done is to get the base record from
   the entity and the column with the collection that we want to update
   table we do so with this statement
     INSERT INTO
     TABLE(select entity.name
           from entity
           where entity.uidn = 2)
   The next part of the insert involves adding a record to the collection
   column
     ('Galvatron',NULL,NULL,NULL,
       NULL,SYSDATE,NULL,USER,NULL)
*/
INSERT INTO
TABLE(select entity.name
      from entity
      where entity.uidn = 2)
VALUES
('Galvatron',NULL,NULL,NULL,
 NULL,SYSDATE,NULL,USER,NULL);
COMMIT;
--> Update specific records inside of column collections
/* We'll use a similiar method to update Megatrons Galvatron's
   NAME record to have a last name
*/
UPDATE TABLE(select entity.name
             from entity
             where entity.uidn = 2)
SET last = 'Decepticon',
    change_date = SYSDATE,
    change_user = USER
WHERE first = 'Galvatron';
COMMIT;
--> Delete specific records inside of column collections
/* Eventually Galvatron's name returns to being Megatron,
   so lets delete out the Galvatron record from the NAME
   collection
*/
DELETE TABLE(select entity.name
             from entity
             where entity.uidn = 2)
WHERE first = 'Galvatron';
COMMIT;

If you find any bugs or want to contact me I can be reached @ doughjoe77@hotmail.com.