Object Views and Nested Tables

Object views allow existing relational data to be mapped to an object-relational model, whilst retaining backwards compatibility with previous relational systems. Nested tables allow a move directly to an object-relational model. This article presents examples of modelling a master-detail relationship using object views and nested tables.

Relational Schema
Object Types
Object View
Nested Table
Cleanup

Relational Schema

In order to define an object view representing a master-detail relationship, we must first create a relational schema.

CREATE TABLE masters (
  id     NUMBER(10) NOT NULL,
  name   VARCHAR2(50) NOT NULL
)
/

ALTER TABLE masters ADD (
  CONSTRAINT masters_pk PRIMARY KEY (id)
)
/

CREATE SEQUENCE masters_seq;


CREATE TABLE details (
  id         NUMBER(10) NOT NULL,
  master_id  NUMBER(10) NOT NULL,
  name       VARCHAR2(50) NOT NULL
)
/

ALTER TABLE details ADD (
  CONSTRAINT details_pk PRIMARY KEY (id)
)
/

ALTER TABLE details ADD (
  CONSTRAINT details_masters_fk
  FOREIGN KEY (master_id)
  REFERENCES masters (id)
)
/

CREATE INDEX details_masters_fk_i
ON details(master_id)
/

CREATE SEQUENCE details_seq;

Object Types

Next we create the object types necessary to map the relational data to an object-relational model. In this example we want the relevant detail data to be presented as a nested table within the master record, so we must define a details row and table type.

CREATE OR REPLACE TYPE t_details_row AS OBJECT (
  id         NUMBER(10),
  master_id  NUMBER(10),
  name       VARCHAR2(50)
);
/

CREATE OR REPLACE TYPE t_details_tab AS TABLE OF t_details_row;
/

Object View

With the relational schema and the object types in place, we are able to create an object view to map the relational data to the object-relational model:

CREATE OR REPLACE VIEW masters_v AS
SELECT  m.id,
        m.name,
        CAST (MULTISET (SELECT  d.id,
                                d.master_id,
                                d.name
                        FROM    details d
                        WHERE   d.master_id = m.id) AS t_details_tab) details
FROM    masters m
/

At this point the relational data can be queried in its object-relational form, but in order to perform DML we must create an INSTEAD OF trigger which actually does the work on the nested table column. The following trigger converts an insert against the view into the relevant inserts for the relational tables.

CREATE OR REPLACE TRIGGER masters_v_trg
   INSTEAD OF INSERT ON masters_v
   FOR EACH ROW
BEGIN
  -- Insert the master record.
  INSERT INTO masters (id, name)
  VALUES (:new.id, :new.name);

  -- Loop through the details collection, inserting each record into the base table.
  FOR i IN :new.details.first .. :new.details.last LOOP
    INSERT INTO details (id, master_id, name)
    VALUES (:new.details(i).id, :new.details(i).master_id, :new.details(i).name);
  END LOOP;
END;
/

With the view and trigger in place we can now access the data in an object-relational manner. The code below inserts a single master record, which contains two details records.

DECLARE
  l_master_id    NUMBER(10);
  l_details_tab  t_details_tab := t_details_tab();
  
  FUNCTION get_next_detail_id RETURN NUMBER AS
    l_detail_id  details.id%TYPE;
  BEGIN
    SELECT details_seq.NEXTVAL
    INTO   l_detail_id
    FROM   dual;
    RETURN l_detail_id;
  END get_next_detail_id;
BEGIN
  -- Get the id of the master record.
  SELECT masters_seq.NEXTVAL
  INTO   l_master_id
  FROM   dual;
  
  -- Populate the details collection prior to insert.
  l_details_tab.extend;
  l_details_tab(l_details_tab.last) := t_details_row(get_next_detail_id, l_master_id, 'Test Child Insert 1');
  l_details_tab.extend;
  l_details_tab(l_details_tab.last) := t_details_row(get_next_detail_id, l_master_id, 'Test Child Insert 2');
  
  -- Insert the data via the object view.
  INSERT INTO masters_v
  (id, name, details)
  VALUES
  (l_master_id, 'Test Master Insert', l_details_tab);

  COMMIT;
END;
/

Once run, we can see that the data has been inserted into the relational tables as expected.

SQL> SELECT * FROM masters;

        ID NAME
---------- --------------------------------------------------
         1 Test Master Insert

1 row selected.

SQL> SELECT * FROM details;

        ID  MASTER_ID NAME
---------- ---------- --------------------------------------------------
         1          1 Test Child Insert 1
         2          1 Test Child Insert 2

2 rows selected.

SQL> SELECT * FROM masters_v;

        ID NAME
---------- --------------------------------------------------
DETAILS(ID, MASTER_ID, NAME)
----------------------------------------------------------------------------------------------------
         1 Test Master Insert
T_DETAILS_TAB(T_DETAILS_ROW(1, 1, 'Test Child Insert 1'), T_DETAILS_ROW(2, 1, 'Test Child Insert 2')
)


1 row selected.

SQL>

Nested Table

The following code creates an object-relational table matching the definition of the masters_v view.

CREATE TABLE masters_2 (
  id        NUMBER(10) NOT NULL,
  name      VARCHAR2(50) NOT NULL,
  details   t_details_tab
)
NESTED TABLE details STORE AS details_2
/

As this is an actual table definition, no trigger is needed to allow DML. The DML operations look identicle to those performed against the object view.

DECLARE
  l_master_id    NUMBER(10);
  l_details_tab  t_details_tab := t_details_tab();
  
  FUNCTION get_next_detail_id RETURN NUMBER AS
    l_detail_id  NUMBER;
  BEGIN
    SELECT details_seq.NEXTVAL
    INTO   l_detail_id
    FROM   dual;
    RETURN l_detail_id;
  END get_next_detail_id;
BEGIN
  -- Get the id of the master record.
  SELECT masters_seq.NEXTVAL
  INTO   l_master_id
  FROM   dual;
  
  -- Populate the details collection prior to insert.
  l_details_tab.extend;
  l_details_tab(l_details_tab.last) := t_details_row(get_next_detail_id, l_master_id, 'Test Child Insert 1');
  l_details_tab.extend;
  l_details_tab(l_details_tab.last) := t_details_row(get_next_detail_id, l_master_id, 'Test Child Insert 2');
  
  -- Insert the data via the object view.
  INSERT INTO masters_2
  (id, name, details)
  VALUES
  (l_master_id, 'Test Master Insert', l_details_tab);

  COMMIT;
END;
/

Querying the table gives similar results to querying the object view.

SQL> SELECT * FROM masters_2;

        ID NAME
---------- --------------------------------------------------
DETAILS(ID, MASTER_ID, NAME)
----------------------------------------------------------------------------------------------------
         2 Test Master Insert
T_DETAILS_TAB(T_DETAILS_ROW(3, 2, 'Test Child Insert 1'), T_DETAILS_ROW(4, 2, 'Test Child Insert 2')
)


1 row selected.

SQL>

Cleanup

The following commands remove all the objects created in the above examples.

DROP VIEW masters_v;
DROP TABLE details;
DROP TABLE masters;
DROP TABLE masters_2;
DROP SEQUENCE details_seq;
DROP SEQUENCE masters_seq;
DROP TYPE t_details_tab;
DROP TYPE t_details_row;
PURGE RECYCLEBIN;

For more information see:

Oracle Database Application Developer's Guide - Object-Relational Features

Hope this helps. Regards Tim...

Back to the Top.