Sometimes interesting problems lead to shock or dismay at the suppositions of why they occur. Why an
ORA-22979 is raised is one of those, and the error is typically:
ERROR at line 1: ORA-22979: cannot INSERT object VIEW REF OR user-defined REF
This error occurs on an
INSERT statement if you follow the example from the Oracle 11gR2 Object-Relational Developer’s Guide, which also has various slightly modified examples in a couple PL/SQL books. It also happens on an
UPDATE statement to populate
The conflict is typically between the uniqueness of the reference and an attempt to make a non-reference column of the object type a primary key constrained column and embedded object view. The source of the conflict is the
OBJECT IDENTIFIER IS PRIMARY KEY associated with a primary key in the Oracle documentation. The two goals are mutually exclusive; only the reference or non-reference column can be the object identifier. Unfortunately, Oracle documentation contains both examples in different places without making any effective cross reference.
If you want to make a column of an object type a primary key for an object table (that is a table that uses an object type to define its structure) and the object view (the content of the embedded object type), you can’t include the
OBJECT IDENTIFIER IS PRIMARY KEY clause when you want to populate the
REF column of the object type. Here’s an example that uses a column of the object type as a primary key and leaves the
REF column empty:
-- Create the BASE_T type, or specification for IDL. CREATE OR REPLACE TYPE base_t IS OBJECT ( obj_id NUMBER , obj_name VARCHAR2(30) , obj_ref REF base_t) NOT FINAL; /
You can then create a table like the following:
CREATE TABLE base OF base_t ( obj_id CONSTRAINT base_pk PRIMARY KEY ) OBJECT IDENTIFIER IS PRIMARY KEY;
Let’s insert some rows to test for ourselves that this fails when you try to assign references:
INSERT INTO base VALUES (base_t(1, 'Dwalin',NULL)); INSERT INTO base VALUES (base_t(2, 'Borfur',NULL)); INSERT INTO base VALUES (base_t(3, 'Gloin',NULL)); INSERT INTO base VALUES (base_t(4, 'Kili',NULL)); INSERT INTO base VALUES (base_t(5, 'Fili',NULL));
UPDATE statement attempts to assign references, but fails as shown below:
UPDATE base b SET obj_ref = REF(b);
UPDATE fails as shown:
UPDATE base b * ERROR at line 1: ORA-22979: cannot INSERT object VIEW REF OR user-defined REF
The simple fix redefines the object table by removing the
OBJ_ID column as an object identifier and primary key value. You do that by removing the
OBJECT IDENTIFIER IS PRIMARY KEY clause because the column of the object type can be a primary key for the table without being an object view identifier. After you make the change, you can successfully update the table with object references. Object identifiers or references are unique and serve the same purpose of a primary key for the object view, and at the same time they can’t both exist.
CREATE TABLE base OF base_t
Inserting the same rows, you can now update the table to provide valid object references. Let’s experiment with how they work because that’s also not as clear as I’d like in the Oracle documentation.
The next statement creates a
CHILD table that holds a reference to the
BASE (or parent) table and another instance of the same
BASE_T object type:
CREATE TABLE child ( child_id NUMBER CONSTRAINT child_pk PRIMARY KEY , base_ref REF base_t SCOPE IS base , child base_t);
INSERT statement can’t use a
VALUES clause because we MUST capture the reference (or in this case primary key) from the
BASE (or parent) table. An
INSERT statement with a query does the trick:
INSERT INTO child SELECT 1, obj_ref, base_t(1, 'Gimli',NULL) FROM base b WHERE b.obj_name = 'Gloin';
You should note that the reference for the
CHILD column isn’t set but is likewise not required for the example to work.
Now, lets perform an standard
INNER JOIN (equijoin) between the two tables by using the references as primary and foreign keys. Please note the trick is referring to the table and column of the
BASE (or parent) table and the table, column, and embedded
OBJ_REF of the CHILD table.
COLUMN father FORMAT A10 COLUMN son FORMAT A10 SELECT b.obj_name AS "Father" , c.child.obj_name AS "Son" FROM base b INNER JOIN child c ON b.obj_ref = c.base_ref.obj_ref;
You get the following results:
Father Son ---------- ---------- Gloin Gimli
You can make a view of this table with either of these syntaxes:
CREATE OR REPLACE VIEW base_v OF base_t WITH OBJECT OID DEFAULT AS SELECT * FROM base;
CREATE OR REPLACE VIEW base_v OF base_t WITH OBJECT OID (obj_id) AS SELECT * FROM base;
Hope it helps anybody trying it. Personally, I think it’s better to use collections of object types, but that’s much bigger discussion that I’ll save for the Oracle Database 12c PL/SQL Programming book that I’m writing.