DBA Hub

📋Steps in this guide1/5

Object Views and Nested Tables

Use object views to present relational data as an object-relational model.

oracle miscconfigurationintermediate
by OracleDba
14 views
1

Relational Schema

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

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
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;
2

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.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
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;
/
3

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. At this point the relational data can be queried in its object-relational form, but in order to perform DML we must create an 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. 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. Once run, we can see that the data has been inserted into the relational tables as expected.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
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
/

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;
/

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;
/

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>
4

Nested Table

The following code creates an object-relational table matching the definition of the masters_v view. 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. Querying the table gives similar results to querying the object view.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
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
/

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;
/

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>
5

Cleanup

The following commands remove all the objects created in the above examples. For more information see: - Oracle Database Application Developer's Guide - Object-Relational Features Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
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;

Comments (0)

Please to add comments

No comments yet. Be the first to comment!